A supermarket (like a Safeway or Lucky store) has asked your help in grouping th
ID: 3677570 • Letter: A
Question
A supermarket (like a Safeway or Lucky store) has asked your help in grouping theattributes listed at the bottom of this document into a set of relational database tables thatare all normalized to third normal form (3NF). The supermarket will then create adatabase and these tables using database software.Here are some of the business rules for this database:
One product may be supplied by one or more suppliers and one supplier may supplyone or more products. Cost of the same product may vary from one supplier toanother. (Cost refers to what the store paid to get the product; price refers to whatcustomer pays to buy the product.)
Each employee has a supervisor (who is also an employee); a supervisor typicallysupervises many employees.
The supermarket has many departments such as dairy products, baked goods, fruitsand vegetables, etc.
One sales transaction may contain one or more products.Please review the list of attributes given below and create a list of tables showing tablename, column names, and the name of the PK column underlined). If necessary, you mayadd a PK attribute to the list of attributes given below.
Prepare and submit a Word document with a listing of tables in 3NF written in the formatshown in the following example. Write your full name at the top of this document.
Example answer:INSTRUCTOR (Instructor-ID, First-Name, Last-Name)CLASS (Class-Code, Class-Title, Instructor-ID)
List of attributes that you need to group into 3NF tables:
Product-ID. Product name. Product department. Product cost. Employee ID. Employeename. Employee phone. Supervisor ID. Supplier ID. Supplier name. Transaction ID.Transaction date. ID of each product would also need to be listed on each transactionalong with quantity purchased and sale price per unit of each product purchased.
Explanation / Answer
Tables and column_names, PK
Product
Product_id
dept
cost
Employee
employee_id
employee name
phone
type
Supervisor
supervisor_id
Supplier
supplier_id
supplier_name
Transaction
trans_id
trans_date
as per given rules: there are some transitive dependencies ....After removing the dependency we'll have following relations:
1. Product(trans_id, trans_date, product_id)
where transaction _id will act as primary key
2. Supplier (supplier_id, supplier_name,product_id,cost)
where supplier_id will act as primary key
3. Supervisor(supervisor_id, employee_id, employee name)
where supervisor_id will act as Pk.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.