Fill This Form To Receive Instant Help
Homework answers / question archive / Your organization has requested that you create a new database to support organizational needs
Your organization has requested that you create a new database to support organizational needs. Based on the information you provided in this week’s Discussion, create a database as defined in the database project.
The project must include at least five tables, and should have no more than 10 tables.
1. Define your database.
o Describe the database, its purpose, why it was created, who it was created for, who the
users are, and what type of processes and functions it will support.
o List the entities of your database.
o Present the business rules that determine connectivity, using the format reviewed in
Week 2.
o Draw the ERD; copy the drawing into your Word project document.
2. Create the database and tables for the database. Show all SQL statements. Include
primary and foreign keys.-SEE BELOW
The organization that I will be creating a database for is the Elite Vision. This group is a national photography firm that captures footage of different milestone and corporate events. From brand development, to video production Elite Vision wants to capture your vision using state of the art technology and digital archiving to save your most precious memories. Our digital professionals and cinematographers make your special day into a fairytale. Photography allows us to artistically paint the picture of a sacred moment and remember it for years to come. Whether it's a sweet sixteen, wedding day, religious ceremony or graduation, Elite Vision can capture those joyous occasions with high resolution photography, photo booth renting and cinematographers all across the US. The organization has a few employees which include the, photographers, cinematographers, accountant, IT manger and visual database artist. The system that is currently being used stores all types of footage, clips and customers information. The Box is where we store files and projects that don't necessarily go to the client. Onlinepictureproof is where proofs are can be retrieved and sent to the client and Zenfolio is where the client can purchase albums, cards and templates of the photos. Since there are thousands of pictures and a very disorganized directory, we currently have several moving parts. This new system will be used to replace Elite Vision’s current database management tools. The new database system will help to swiftly and safely ease photography storage. It will also help with large files and we will now have the ability to easily share an abundance of data and eliminate duplication, minimize client error upon entry, increase productivity and allow each client to have access of their proofs. In conjunction with The Box, MY SQL will be used to manage and store client data through the server from the main office. As photographs are uploaded in a file, the client's information including last name, first name, home address, email and phone number will be entered.
Employees- Entity 1
Customer- Entity 2
Photos- Entity 3
Location- Entity 4
Album- Entity 5
Tag- Entity 6
Business Rules are as followed: Customers and Employees
Many customers can be assigned to only one employee where one employee can be assigned to multiple customers. Customer M: 1 Employee
Employee 1: M Customer
Photo and Location
Many photos can be taken in one location and one location can be assigned to many photos
Photos M: 1 Location
Location 1: Photos
Photos and Album
One Album can be assigned to many photos, and one Album can be assigned to many photos
Album 1: M Photos
Photos M: 1 Album
Employees- Entity 1
Employee_ID Code (PK) (FK) |
Last_Name |
First_Name |
Street_Address |
City |
State |
Zip_Code |
6 |
Jacobs |
Martha |
123 Main Rd |
Grant |
MI |
49219 |
6 |
Jacobs |
Martha |
123 Main Rd |
Grant |
MI |
49219 |
12 |
Smith |
Wallace |
102 Raymnond St |
Ada |
MI |
49441 |
12 |
Diaz |
Michael |
419 Harper Rd |
Lansing |
MI |
49224 |
24 |
Diaz |
Michael |
419 Harper Rd |
Lansing |
MI |
49224 |
Customer- Entity 2
Customer_Code (PK) |
Last_Name |
First_Name |
Phone_Number |
|
Address |
Employee_Code (FK) |
1111 |
Perry |
Tyler |
6173437781 |
Tyler.perry@yahoo.com |
4 Oak Blvd, MA |
6 |
3113 |
Winfre |
Oprah |
8753442134 |
Oprah.winfrey@gmail.com |
48 Darling , MI |
24 |
3152 |
Hanks |
Tom |
4132212290 |
Tomhanks@outlook.com |
914 Cherry , MI |
12 |
4051 |
Bundy |
Al |
2173900122 |
albundy@yahoo.com |
519 Watson, MI |
24 |
Photos Entity 3
Photo_ID Code (PK) |
Customer_ID Code (FK) |
Album_ID (FK) Code |
Location_ID Code (FK) |
Title |
Description
|
Uploaded_Date
|
Image_Path |
Tag_ID (FK) |
12489 |
1111 |
AXK2 |
BOS |
Wedding |
Wedding Party |
02-JUL-11 |
C:/photos |
001 |
12491 |
3113 |
AZO5 |
UT |
Graduation |
Graduation Party |
02-JUL-11 |
C:/photos |
002 |
12494 |
3152 |
BHI2 |
FLD |
Funeral |
Burial Ceremony |
04-JUL-11 |
D:/photos |
003 |
12495 |
4051 |
BTB4 |
SC |
First Birthday |
First Birthday Party |
04-JUL-11 |
C:/photos |
004 |
12498 |
4120 |
BZU6 |
LV |
Wedding |
Wedding Party |
05-JUL-11 |
F:/photos |
005 |
Location- Entity 4
Location_ID (PK) |
Location_Name |
BOS |
Boston |
UT |
Utah |
NY |
New York |
SC |
Sacramento |
NJ |
New Jersey |
Tag_Photo- Entity 6
Tag_ID (PK) |
Tag_Description |
001 |
Family |
002 |
Friends |
003 |
School Mates |
004 |
Employees |
005 |
Colleagues |
Album Entity 5
Album_ID (PK) |
Title |
Description |
AXK2 |
Lake |
Photos taken in the Blvd Lake |
AZO5 |
Field |
Photos taken in an open field |
BHI2 |
Water |
Photos taken at the local pool |
BTB4 |
Sports |
Photos taken during a sport event |
BZU6 |
School |
Photos taken during graduation, matriculation |