Assignment-1

.docx

School

New Jersey Institute Of Technology *

*We aren’t endorsed by this school

Course

631

Subject

Computer Science

Date

Jan 9, 2024

Type

docx

Pages

11

Uploaded by LieutenantQuetzal4061

Report
CS631-101 Assignment1 Fall 2023 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 October 25, 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. 1
CS631-101 Assignment1 Fall 2023 Problem 1 (15 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 less than 10 hours, list their First Name, Last Name, their manager’s name, the Project Number, the Project Name, and the hours worked on that project. Ans. From the given table, employee’s details who worked on any project for less than 10 hours. First Name Last Name Manager’s Name Project Number Project Name Hours Worked on the Project John Smith Franklin Wong 2 Product Y 7.5 Ahmad Jabbar Jennifer Wallace 30 New Benefits 5.0 Problem 2 (10 points) Determine the cardinality ratio for each relationship shown below based on the attached Company table. (2pts each): a. Employee ______ Department b. Employee ___ Project c. Department ____ Project d. Employee______ Dependent e. Department ______Dept_Locations Explain your answers and list any assumptions you consider significant. Assumptions are required to be “real-world”, reasonable, and accurate. Ans . A . Employee ______ Department Based on the provided data, each employee is associated with one department through the department number. Therefore, each employee is related to only one department, and a department can have multiple employees. So, the cardinality ratio for the relationship between Employee and Department is indeed N:1 (Many-to-One), where "N" represents the "many" employees related to "1" department. B. Employee ___ Project 2
CS631-101 Assignment1 Fall 2023 Based on the provided data, we can see that the relation between employee and project is determined from the table "Works_On" . We can see that an employee is working on either one or two projects and each project is having two or more employee working on it. So, the cardinality ratio for the relationship between Employee and Project is M:N(Many-to-Many). C. Department ____ Project Based on the provided data, we can see that the department and project are linked through department number. Each project is only associated with only one department number, and each department has one or more project numbers. So, the cardinality ratio for the relationship between Department and Project is 1:N, where "N" represents the "many" projects related to "1" department. D. Employee______ Dependent Based on the provided data, we can see that the employees and dependents are linked through SSN. Some employees have no dependents, while others may have one or more dependents and Each dependent is associated with only one employee. So, the cardinality ratio for the relationship between Employee and Dependent is 1:N(One-to-Many), or more specifically, 0:N, where "N" represents the "many" dependents related to "0" or "1" employee, accounting for the possibility that some employees may have no dependents. E. Department ______Dept_Locations Based on the provided data, we can see that the department and dept_locations are linked through department number Each department number can have one or more dept_locations, and each dept_location is related to one department number. So, the cardinality ratio for the relationship between Department and Dept_Locations is 1:N, where "N" represents the "many" dept_locations related to "1" department. 3
CS631-101 Assignment1 Fall 2023 Problem 3 (15 points) Given the relation below and the associated functional dependencies: Y = {M, N, O, P, Q, R, S,T, U, V, W} R ST M PQ P UV N R MN O (a) Determine the Primary Key of Y Ans. (b) Put the table in Second Normal Form Ans. 2NF = 1NF + FFD M PQUV N RST MN O (c) Put the table in Third Normal Form Ans. 3NF = 2NF + No Transitivity M PQ P UV N R R ST MN O 4 1. M functionally determines P & Q and P functionally determines U, V which implies M functionally determines U, V. so; P cannot be primary key. 2. N functionally determines R and R functionally determines S, T which implies N functionally determines S, T, so R cannot be primary key. 3. W is functionally dependent on itself. 4. So, the primary key is Y = {M, N , O, P, Q, R, S, T, U, V, W } R1 = {M , P, Q, U, V} R2 = {N , R, S, T} R3 = {M, N , O} R4 = {W } R11 = {M , P, Q} R12 = {M , U, V} R21 = {N , R} R22 = {N , S, T} R31 = {M, N , O} R4 = {W } S T P Q U V R O R M P N
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
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
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: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
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
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:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
SEE MORE TEXTBOOKS