CS3410_Assignment3

docx

School

Kennesaw State University *

*We aren’t endorsed by this school

Course

3410

Subject

Computer Science

Date

Dec 6, 2023

Type

docx

Pages

4

Uploaded by ColonelRose5100

Report
CS 3410:Introduction to Databases College of Computing and Software Engineering Kennesaw State University Assignment 3 I .Regional Labs Case Questions (8 pts) Regional Labs is a company that conducts research and development work on a contract basis for other companies and organizations. Figure shows data that Regional Labs collects about projects and the employees assigned to them. This data is stored in a relation (table) named PROJECT: ProjectI D EmployeeNa me EmployeeSal ary 100-A Eric Jones 64,000.00 100-A Donna Smith 70,000.00 100-B Donna Smith 70,000.00 200-A Eric Jones 64,000.00 200-B Eric Jones 64,000.00 200-C Eric Parks 58,000.00 200-C Donna Smith 70,000.00 200-D Eric Parks 58,000.00
PROJECT (ProjectID, EmployeeName, EmployeeSalary) a) Assuming that all functional dependencies are apparent in this data, which of the following are true? 1. ProjectID EmployeeName No because 100-A can mean either Eric Jones or Donna smith 2. ProjectID EmployeeSalary No 3. (ProjectID, EmployeeName) EmployeeSalary True 4. EmployeeName EmployeeSalary True 5. EmployeeSalary ProjectID False 6. EmployeeSalary (ProjectID, EmployeeName) False b) What is the primary key of PROJECT? Project ID should be the primary key c) Are all the non-key attributes (if any) dependent on the primary key? No d) In what normal form is PROJECT? 1NF e) Describe two modification anomalies that affect PROJECT. 1)To have an employee, employee first must be assigned to a project 2)If salary changes then multiple values must be changed leading to inconsistent data f) Is ProjectID a determinant? If so, based on which functional dependencies in part A? No project ID is not a determinant g) Is EmployeeName a determinant? If so, based on which functional dependencies in part A? Yes, EmployeeName determines EmployeeSalary h) Is (ProjectID, EmployeeName) a determinant? If so, based on which functional dependencies in part A? Yes, it determines EmployeeSalary i) Is EmployeeSalary a determinant? If so, based on which functional dependencies in part A? In this table EmployeeSalary may determine EmployeeName but it’s not in part A j) Does this relation contain a transitive dependency? If so, what is it? No k) Redesign the relation to eliminate modification anomalies.
l) PROJECT(ProjectID, EmployeeName) and SALARY (EmployeeName, EmploySalary) 2. Consider the table: ( 12 pts) STAFF_MEETING (EmployeeName, ProjectName, Date) The rows of this table record the fact that an employee from a particular project attended a meeting on a 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. 1. State the functional dependencies in STAFF_MEETING. ProjectName->EmployeeName, Date 2. Transform this table into one or more tables in BCNF. State the primary keys, candidate keys, foreign keys, and referential integrity constraints. STAFF( EmployeeName, ProjectName) STAFF_MEETING( ProjectName, Date) 3. Is your design in part B an improvement over the original table? What advantages and disadvantages does it have? It removes redundant data. Consider the table: STUDENT (StudentNumber, StudentName, 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. 4. State any multivalued dependencies in STUDENT. StudentNumber->StudentName StudentNumber->Nickname StudentNumber->Club 5. State the functional dependencies in STUDENT. StudentNumber->(StudentName,Dorm,RoomType,Sibling) RoomType->DormCost Club->ClubCost
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
6. Transform this table into two or more tables such that each table is in BCNF and in 4NF. State the primary keys, candidate keys, foreign keys, and referential integrity constraints. STUDENT_1( StudentNumber, StudentName, Dorm, Roomtype, DormCost, Sibling) STUDENT_2( StudentNumber, Club, ClubCost) Student_3( StudentNumber , Nickname)

Related Documents

Strat HW 04 Decision Analysis homework.pptx
OTCL 520 Exam 2.docx
Discussion over Week 7 Problems_SU21.pdf
HW 3.docx
infograph script first draft.docx
PBDMS HW3 (2).docx
PBDMS HW3 (1).docx
Lab 1 - Debugging New.docx
lb 3.pdf
Lab1-EIGRP.pdf
Lab 9 - Ethernet and ARP.docx
Assignment 3 UC.docx
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781285867168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781285867168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS