Lab 4 ADBC2 - Database Design Review Animations Functional Dependencies To access the Database Design Module, go to: http://adbc2.kennesaw.edu/ Normalization Anomalies Functional Dependencies In this section go through the following in the Scenarios section: ► Introduction Help (Read instruction on how to use this sub module) Hospital scenario University scenario Stocks scenario Normalization In this section go through the following in the Scenarios section: Introduction Help (Read instructions on how to create tables and designate Primary Keys) Employee scenario Auto Theft Scenario Property scenario Customer scenario Medicine scenario Anomalies In this section go through the following in the Scenarios section: Introduction Help (Read instruction on how to use this sub module) Insert 1 scenario Insert 2 scenario Update 1 scenario Update 2 scenario Delete 1 scenario Delete 2 scenario Lab 4 ***Submit as a Microsoft Word document in Dropbox area of D2L titled Lab 4 submission *** Functional Dependencies Determine functional dependencies of the follow tables. When stating your answers use standard notation for showing functional dependencies. Example: SKU → SKU_Description, Department, Buyer 1. Consider the table: STAFF MEETING (EmployeeName, ProjectName, Date) The rows of this table record the fact that an employee from a particular project attended a meeting on the given date. Assume that a project meets at most once per day. Also, assume that only one employee represents a given project, but that employees can be assigned to multiple projects. State the functional dependencies only. 2. Consider the table: STUDENT (Number, Name, Dorm, RoomType, DormCost, Club, ClubCost, Sibling, Nickname) Assume that students pay different dorm costs, depending on the type of room they have, but that all members of a club pay the same cost. Assume that students can have multiple nicknames. State the functional dependencies only. 3. Normalize the following table into a set of tables that puts them into 3NF. Write your answer in the standard format: Example of Standard format - STUDENT(Stid, Sname,Classid) 4. The data provided below is in one table. Put the data in this table into 3rd normal form (3NF). [Note: Eid = Employee id, Pid = project id, Mid Manager id] A 1 PetName 2 Kino 3 Teddy 4 Filo 5 AJ 6 Cedro 7 Woolley 8 Buster Eid E01 Type Dog Cat Dog Dog Cat E02 E03 E04 B Cat Dog Ename John p2 p2 p3 a) Identify functional dependencies. Mary Mark Travis b) Normalize the listing into 3NF. Normalization C Breed Std. Poodle Cashmier Std. Poodle Collie Mix Unknown Unknown Border Collie DOB Pid pl ??? Normalize the Veterinary Office List shown in the figure below. D E Owner 27-Feb-05 Marsha Downs 1-Feb-04 Richard James 17-Jul-06 Marsha Downs 5-May-05 Liz Frier 6-Jun-01 Richard James Richard James 11-Dec-00 Miles Trent a) Identify functional dependencies. Pname Eco DBMS Admin Admin Educ. b) Normalize the listing into 3NF. Mid m1 F OwnerPhone 555-123-6788 555-444-0098 555-123-6788 555-444-5596 555-444-0098 555-444-0098 555-999-8861 m2 m2 m1 = G Mname Becky Joe Joe Becky OwnerEmail MD@somewhere.com RJ@somewhere.com MD@somewhere.com LF@somewhere.com RJ@somewhere.com RJ@somewhere.com MT@somewhere.com H Service Ear Infection Nail Clip One year shots Nail Clip Skin Infection Laceration Repair J Date Charge 17-Aug-06 $ 65.00 5-Sep-06 $ 27.50 5-May-06 $ 5-Sep-06 $ 3-Oct-06 $ 5-Oct-06 $ 42.50 27.50 35.00 127.00 5. Normalization Matching. Indicate which normal form, each of the following conditions represents. [1NF, 2NF, 3NF, 4NF] 1) No Partial Dependencies 2) No multi-value attributes 3) No dependency between non-key attributes 4) All attributes are dependent on the key 5) All attributes are dependent on the whole key 6) All attributes are dependent on nothing but the key 7) Similar to BCNF 8) No transitive dependencies 9) For a table to be considered a relation, it must at least satisfy 6. For the following, answer whether Tables are in 3NF? If No, State the normalization rule that is violated. Rules can be: A) No multi-valued attribute B) Not dependent on the whole primary key C) Dependencies between non-key attributes. 1) Employee (ssn, Name, Salary, Address, ListOfSkills) Yes No Violates Rule: 2) Vehicle (LicensePlate, Brand, Model, PurchasePrice, Year, OwnerSSN, OwnerName) Yes No Violates Rule: 3) Employee (ssn, Name, Salary, did) Yes No Violates Rule: Normal Form 4) Customer (Cust_Id, Name, Salesperson, Region) where Salesperson determines Region. Yes No Violates Rule: 5) Component (ItemNo, ComponentNo, ItemName, Quantity) where ItemNo -> ItemName No Violates Rule: Yes