Assignment-1

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 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

CS631-101 Assignment1 Fall 2023 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. Ans. Maybe . The Actor and Movie has M:N relation with respect to performs_in, which implies that an actor can act in multiple movies and a movie can have multiple actors. In the data base there is a possibility, that an actor may or may not have acted in more than 5 movies. b. An actor can only be the lead in multiple movies. Ans. Yes . The Actor and Movie has 1:N relation with respect to lead role which implies that an actor can work in multiple movies as a lead role, but the movie can only have only one lead actor. 5

CS631-101 Assignment1 Fall 2023 G  AB EG  K EJ  ACDFH d. Put the table in Third Normal Form Ans. 3NF = 2NF + No Transitivity G  AB EG  K EJ  ACD D  F C  H Problem 6: (10 points) Given the Relation Y = {P, Q, R, S, T} with the following dependencies: {P, Q}  S {R, S}  T {S, T}  P  For the attributes to be a candidate key, the closure of the attributes should give all the attributes. a. Is {P, Q} a candidate key of Y? Explain (4 pts) Ans. [PQ] +  PQST : {P,Q} is not a candidate key because the closure of PQ is not giving all the attributes of Y, that is, it is missing the attribute R. b. Is {P,Q,R} a candidate key of Y? Explain (3pts) Ans. [PQR] +  PQRST : {P,Q, R} is a candidate key because the closure of PQR is giving all the attributes of Y. c. Is {P, Q, R, S} a candidate key of Y? Explain (3 pts) Ans. [PQRS] +  PQRST : {P,Q, R, S} is a candidate key because the closure of PQRS is giving all the attributes of Y. 7  R11 = {G , A, B}  R21 = {E, G , K}  R31 = {E, J, A, C, D}  R32 = {E, J, F}  R33 = {E, J, H}

CS631-101 Assignment1 Fall 2023 Problem 7 (10 points): You are given a single-table database of the following manager-employee relations. Column Heading Explanation MID Manager unique identifier MFNAME Manager first name MLNAME Manager last name EEFNAME Employee first name EELNAME Employee last name EEPHONE# Employee phone number EEADDRESS Employee address EESEX Employee sex Assume the company has one level of manager and that managers are considered separate from employees. Assume each manager has a unique identifier (MID). Also, assume each manager can have multiple employees, but those employees’ name will always be unique per manager (but not necessarily unique across the whole table, i.e., no manager will have two employees with the same name, but two managers may have employees named John Smith). What would the primary key be? Design the table accordingly, but you may not create any new columns . Show the attributes and a few sample records (enough to make your thinking clear). Ans. Under the constraint of not creating new columns, we can consider a combination of the existing attributes to form a composite primary key. We will use "MID" (Manager unique identifier) along with the combination of the employee's first name ("EEFNAME") and last name ("EELNAME") to create a composite primary key. This combination should ensure that each employee is uniquely identified under a manager, as employees are considered unique per manager. Table Name: Manager_Employee Relations Attributes: MID (Manager unique identifier) MFNAME (Manager first name) MLNAME (Manager last name) EEFNAME (Employee first name) EELNAME (Employee last name) EEPHONE# (Employee phone number) EEADDRESS (Employee address) EESEX (Employee sex) Sample Record : 8

CS631-101 Assignment1 Fall 2023 Table Name: Manager_Employee Relations Attributes: MID (Manager unique identifier) MFNAME (Manager first name) MLNAME (Manager last name) EmployeeID (Employee unique identifier, Primary Key) EEFNAME (Employee first name) EELNAME (Employee last name) EEPHONE# (Employee phone number) EEADDRESS (Employee address) EESEX (Employee sex) Sample Records: MID MFNAME MLNAME EmployeeID EEFNAME EELNAME EEPHONE EEADDRESS EESEX 1 John Doe 1 John Smith (732) 1236-456 731 Fondren, Houston, TX M 1 John Doe 2 Franklin Wong (345) 4565-789 638 Voss, Houston, TX M 2 Jane Smith 3 Alicia Zelaya (551) 5678-843 3321 Castle, Spring, TX F 3 Mark Wilson 4 Jennifer Wallace (345) 3489-934 291 Berry, Bellaire, TX F In this design, the "EmployeeID" column is introduced as a new unique identifier for each employee within the table. This column is used as the primary key to ensure uniqueness, regardless of whether employee names are unique per manager. Correct Answer is adding company email address – MID + EEFName + EELName. 10

CS631-101 Assignment1 Fall 2023 11