Lab 3: Normalization Assignment: Problem #7 from Chapter #6. (Be sure to note th
ID: 3907399 • Letter: L
Question
Lab 3: Normalization
Assignment: Problem #7 from Chapter #6.
(Be sure to note that this is PROBLEM and not REVIEW QUESTION).
Normalize a set of data to 1st, 2nd and 3rd normal form by creating relational schemas, dependency diagrams and an Entity Relationship Diagram (crow’s foot notation).
For this lab, complete problem #7 in Chapter 6. Be sure to complete each letter (a thru c) for the problem. The ERD for letter c must be completed with the use of Microsoft Visio.
Remember to include headers on all documents you submit to include a brief descriptive title, you name and a date.
Attach all files as a single submission to Canvas.
NOTES TO HELP YOU COMPLETE YOUR LAB
Relational schema is defined in section 4.1.b in chapter 4 of the textbook.Dependency diagram is defined in section 6.3 in chapter 6 of the textbook.An example of a crow’s foot ERD can be found in chapter 4, figure 4.35.
attributes that meet proper design criteria, i.e., make sure there are no multival. ued attributes, that the naming conventions are met, and so on.) c. Draw the relational diagram. d. Draw the Crow's Foot ERD 7. Suppose you are given the following business rules to form the basis for a database design. The database must enable the manager of a company dinner club to mail invitations to the club's members, to plan the meals, to keep track of who attends the dinners, and so on. Each dinner serves many members, and each member may attend many dinners A member receives many invitations, and each invitation is mailed to many members. . A dinner is based on a single entree, but an entree may be used as the basis for many dinners. For example, a dinner may be composed of a fish entree, rice, and corn, or the dinner may be composed of a fish entree, a baked potato, and string beans. Because the manager is not a database expert, the first attempt at creating the data- base uses the structure shown in Table P6.7. a. Given the table structure illustrated in Table P67 uitExplanation / Answer
Below are the Normalization form
Let me know in case you have any query.
mst - Master tables
trn - Transactional table where data frequnelty changed
mst_members - Member Details
Attribute Name
Data Type
Description
m_Id
Primary key (Auto Increment)
m_first_name
Varchar 50
m_last_name
Varchar 50
m_address
Varchar 250
m_city
Varchar 50
m_zipcode
Varchar 50
m_number
integer
Created_date
Date
mst_dinner - Dinner details
Attribute Name
Data Type
Description
din_Id
Primary key (Auto Increment)
din_ent_code
(foreign key)
From mst_entries
din_date
Date
din_code
Varchar 50
We can add our own logic to generate code (exp : DIN001)
din_decription
Varchar 100
Created_date
Date
mst_invite - Invitation Details with dinner
Attribute Name
Data Type
Description
inv_Id
Primary key (Auto Increment)
inv_dinner_code
Varchar 50 (foreign key)
Form mst_dinner
inv_number
integer
inv_date
Date
inv_address
Varchar 250
Created_date
Date
trn_invitation_sent - Hold invitation sent details
Attribute Name
Data Type
Description
trn_inv_Id
Primary key (Auto Increment)
trn_m_id
Integer (foreign key)
From mst_member
trn_inv_id
Varchar 50 (foreign key)
Form mst_invite
inv_status
Varchar 50
(Sent,Accepted,Rejected)
inv_accepted_date
Date
inv_dinner_attended
Varchar 10
(Yes,No)
Created_date
Date
mst_dish - Disha details
Attribute Name
Data Type
dish_id
Primary key
dish_name
Varchar 50
dish_description
varchar 50
dish_type
Varchar 50
dish_price
Varchar 50
dish_created_date
Date
mst_entries - Entries details
Attribute Name
Data Type
Description
ent_id
Primary key (Auto Increment)
ent_code
Varchar 50
We can add our own logic to generate code (exp : ENT001)
ent_description
Varchar 50
ent_created_date
Date
trn_entries_dish - etry having many dishes
Attribute Name
Data Type
Description
trn_id
Primary key (Auto Increment)
trn_ent_id
Foreign Key
From mst_entries table
trn_dish_id
Foreign Key
From mst_dish table
Created_date
Date
Attribute Name
Data Type
Description
m_Id
Primary key (Auto Increment)
m_first_name
Varchar 50
m_last_name
Varchar 50
m_address
Varchar 250
m_city
Varchar 50
m_zipcode
Varchar 50
m_number
integer
Created_date
Date
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.