annotated-Assignment1

.pdf

School

New Jersey Institute Of Technology *

*We aren’t endorsed by this school

Course

631

Subject

Computer Science

Date

Apr 29, 2024

Type

pdf

Pages

12

Uploaded by ajakkaoaoanamwuwoo on coursehero.com

CS631-102 Assignment1 Spring 2023 1 Please state any assumptions you are making. Assumptions are required to be “real - world” , reasonable, and accurate. Guidelines: 1. All problems must be solved by hand. Do not submit any SQL code. It will be ignored. 2. Check your work carefully. I am not very sympathetic to errors that would easily turn up if checked. 3. Do not assume files/tables will not change in size in the future unless stated so. 4. Please state any assumptions you are making. Assumptions are required to be “real - world”, reasonable, and accurate. 5. When primary keys are involved, think minimal . 6. Certainly, if you have questions, you are welcome to call me (908-418-6078) or send an email. Submission Requirements: 1. This assignment is due Wednesday March 1, 2023, at 6pm. 2. The assignment must be submitted via Canvas. a. It must be readable, and it is your responsibility to confirm this. If I cannot read it, you run the risk of getting a zero for the problem or the assignment. 3. All work must be your own. The only person you may discuss the assignment with is me (Professor Forman) a. You may NOT discuss problems with any other student. b. You may NOT get answers from sites such as Chegg or Homework Hero or any other online site. c. Anything not mentioned, that constitutes “ not doing your own work” will be considered cheating. d. Violation of these requirements will result in a grade of 0. 4. Submit one document only, unzipped. a. Handwritten problems will not be accepted unless permission is granted by ME.
CS631-102 Assignment1 Spring 2023 2 Problem 1 (10 points) Using the Company database attached at the end of the assignment, provide the following information in a neatly designed table. For any project in which an employee worked more 25 hours, list their First Name, Last Name, their manager’s name, the Project Number, the Project Name, and the hours worked on that project. EEFNAME EELNAME MFNAME MLNAME PNUMBER PNAME HOURS John Smith Franklin Wong 1 ProductX 32.5 Ramesh Narayan Franklin Wong 3 ProductZ 40.0 Ahmad Jabbar Jennifer Wallace 10 Computerization 35.0 Alice Zelaya Jennifer Wallace 30 Newbenefits 30.0
CS631-102 Assignment1 Spring 2023 3 Problem 2 (10 points) Determine the cardinality ratio for each relationship shown below based on the attached Company table. (2pts each): Explain your answers and list any assumptions you consider significant. Assumptions are required to be “real - world”, reasonable, and accurate. a. Employee __N:1____ Department According to the attributes Fname, Dno, Dnumber from Employee and Department tables, there is more than one employee that belong to same department number. But each employee belongs to one department number . assuming each employee belongs to only one department. b. Employee _N:M__ Project According to the attributes Fname, Dno, Dnum from Employee and Project tables, an employee can work on multiple projects and a project can have multiple employees working on it. Assuming more than one employee can involve in more than one project. c. Department __1:N__ Project According to the attributes Dnumber, Pnumber and Dnum from Department and Project tables, a department can have multiple projects but a project belongs to only one department. Assuming 2 departments cannot have one project. d. Employee___1:N___ Dependent According to the attributes Ssn, Essn, Dependent_name from Employee and Dependent tables, an employee can have multiple dependents but a dependent can only belong to one employee. Assuming dependents don’t belong to more than one employee e. Department __M:N___Dept_Locations According to the attributes Dnumber, Dnumber, Dlocation from Department and Dept_Locations, a department can be in multiple locations, but a location can have only one department. Assuming a department is present in multiple locations and a a location can have multiple departments.
CS631-102 Assignment1 Spring 2023 4 Problem3 (15 points) Given relation Y = (A,B,C,D,E,F,G,H,J,K,L} and the following functional dependencies: F → GH A → DE D → JK B → F AB → C (a) Determine the Primary Key of Y Primary key of Y: {A, B, L} A,B are dependants but not determinants and L is not in any of the functional dependency. So, First the candidate key: closure of {A,B,L} = {A,B,L,D,E,F,C,J,K,G,H}. Other than {A,B,L} none of them determine all the attributes in Y. So, The candidate key is {A, B, L}. Now the minimal attributes of {A,B,L} i.e. A,B,L are not super keys. So, {A,B,L} is the primary key of Y. (b) Put the table in Second Normal Form Candidate key of Y:{A, B, L}. So prime attributes: {A,B,L} and non prime attributes: {C,D,E,F,G,H,J,K,L}. Here we have partial dependency for A->DE and B->F. To prevent this, We split Y into 4 sub relations. Y1(A,B,C,E,F,G,H,J,K,L) Y2(A,D,E) Y3(B,F) Y4(A,B,C) F->GH A->DE B->F AB->C D → JK (c) Put the table in Third Normal Form We need to identify if there are any transitive dependencies. All of them do not have any transitive dependency because none of them have a non prime attribute functionally dependent on proper subset of {A,B,L}. So the table is in third normal form. Y1(A,B,C,E,F,G,H,J,K,L) Y2(A,D,E) Y3(B,F) Y4(A,B,C) F->GH A->DE B->F AB->C D → JK
CS631-102 Assignment1 Spring 2023 5 Problem 4 (10 points) Consider the schema below for a database named MOVIES. Using the cardinality constraints shown in the schema below, answer the following questions as True, False, or Maybe. Explain your answer. Use MAYBE for statements that although not explicitly shown as True cannot be shown to be false based on the schema as it is shown. Hint: Do not use your knowledge of the movie industry to answer these questions. Your answer should only be based on the diagram below. a. There are some actors who have acted in more than 5 movies. A) MAYBE because cardinality ratio for ACTOR and MOVIE is M:N and also we cannot specially say some actors have acted in exactly more than 5 movies. Because they could be 4 or 3 or 2. b. An actor can only be the lead in one movie. A) False because the cardinality ratio is 1:N for lead role and he could act as lead role for multiple movies. c. A movie can have only one lead actor. A) True because MOVIE to ACTOR cardinality ratio is N to 1. It means one movie can have only one lead actor. d. Meryl Streep has acted in multiple movies. A) MAYBE because we don’t know if he is ACTOR_PRODUCER or PRODUCER and we don’t know if he acted in acted in single or multiple movies. e. A movie has exactly one director A) True because the cardinality is N:1. So according to that a movie cannot have more than one director.
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
Recommended textbooks for you
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
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
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
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
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
SEE MORE TEXTBOOKS