CSCI 475 Midterm Study Guide (Question part)

Design Question: Draw an ERD

[Department] --- (Offers) --- [Course] | (PK: DepartmentID)

Normalization Question: What is functional dependency? How can it be corrected?

3. 1. A functional dependency occurs when the value of one attribute (or a set of attributes) in a table uniquely determines the value of another attribute. In other words, if attribute X determines attribute Y, then Y is said to be functionally dependent on X. 2. Correcting Partial Dependencies: 2.1 Move non-key attributes that are only partially dependent on part of a composite key to a new table. Ex: (StudentID, CourseID) → Grade StudentID → StudentName 2. Solution: Split into two tables: Student (StudentID, StudentName) Enrollment (StudentID, CourseID, Grade) 3. Correcting Transitive Dependencies: 1. Remove transitive dependencies by placing the dependent attribute in a new table. Ex: Course → Instructor Instructor → Department 2. Solution: Split into two tables: Courses (CourseID, CourseName, InstructorID) Instructor (InstructorID, InstructorName, Department)

Design Question: Identify primary and foreign keys

Identify Primary Keys (PK): 1. The Primary Key uniquely identifies each record in a table. 2. It must be unique and not null for each row. 3. Example: Student table: StudentID (PK), Course table: CourseID (PK) Identify Foreign Keys (FK): 1. Foreign Keys are used to establish relationships between tables. A foreign key in one table references the Primary Key in another table. 2. Example: In an Enrollment table:StudentID (FK) references Student(StudentID), CourseID (FK) references Course(CourseID)

Normalization Question: What is transitive dependency? How can it be corrected?

1. A transitive dependency occurs when a non-key attribute in a table is indirectly dependent on the primary key through another non-key attribute. In simpler terms, if attribute A determines attribute B, and attribute B determines attribute C, then C is transitively dependent on A throught B Ex: StudentID → Course → Instructor 2. To correct a transitive dependency, we need to remove the dependency by splitting the table into two or more tables. The goal is to ensure that non-key attributes only depend on the primary key and not on other non-key attributes. 2.1 Identify the Transitive Dependency: Find non-key attributes that depend on other non-key attributes instead of directly depending on the primary key. 2.2 Move the Dependent Attributes to a New Table: Move the attributes involved in the transitive dependency to a new table where they depend directly on the new key. 2.3 Create a Foreign Key Relationship: Establish a foreign key relationship between the original table and the new table to maintain the connection.

Normalization Question: Be able to take one table and create a set of tables such that all are in 3NF (e.g., look at the unnormalized Employee/Skill table - how did we create 3NF tables)

1. Unnormalized Table (UNF): The unnormalized table might have repeating groups and redundant data 2. Convert to 1NF (First Normal Form): 1. Eliminate repeating groups. 2. Each attribute should hold atomic values (one value per column). 3. Convert to 2NF (Second Normal Form): 1. Remove partial dependencies. Attributes should depend on the entire primary key. 2. In the current table, DeptName depends only on DeptID (not on EmpID), which violates 2NF. 4. Convert to 3NF (Third Normal Form): Remove transitive dependencies. Non-key attributes should depend only on the primary key. Conclusion: 1NF: Atomic values, no repeating groups. 2NF: No partial dependencies (non-key attributes depending only on part of a composite key). 3NF: No transitive dependencies (non-key attributes depend only on the primary key).

Anomalies Question: Identify Update, Delete and Insert anomalies

1. Update: An Update Anomaly occurs when you need to update the same piece of information in multiple places due to redundancy in the database. If the update is not performed everywhere, the database becomes inconsistent. Ex: Empid: 1, 2, 3. EmpName: John, Jane, Bob DeptId: 101, 101, 102 DeptName: Hr, Hr, Marketing 1.2 If the DeptName for DeptID 101 (HR) changes to "Human Resources," you need to update the department name in multiple rows. Failure to update all rows will lead to inconsistent data. Fix: Normalize the table to remove redundancy, e.g., split it into Employee and Department table 2.Delete: A Delete Anomaly occurs when deleting a record unintentionally removes additional, valuable data that should not be deleted Ex: Empid: 1, 2, 3 EmpName: John, Jane, Bob ProjectId: 201, 202, 201, ProjectName: Website, DB, Website 2.2 If you delete the record for John, you may unintentionally delete information about the "Website" project, even though Bob is also working on it. Fix: Normalize the table, separating Employee and Project into their own tables, and use a join table to represent the many-to-many relationship. 3. Insert: An Insert Anomaly occurs when you cannot insert data into a table without also inserting unrelated data due to improper schema design. Ex: EmpID: Null EmpName: Null DeptID: 101 DeptName: HR 3.1 If you want to add a new department (HR) without yet adding employees, you cannot, because the table design forces you to insert an employee record as well. This causes an Insert Anomaly. Fix: Normalize the schema by creating a Department table where departments can be added independently of employee records.

Normalization Question: Be able to differentiate unnormalized, 1NF, 2NF, 3NF tables/schemas

1. Unnormalized Form (UNF): 1. Data is not structured properly; it may contain repeating groups and is stored in an unorganized format. 2. Ex: StudentID Name Courses ----------------------------------- 1 John Math, Physics 2 Jane Math, Chemistry, Physics 2. First Normal Form (1NF): 1. Remove repeating groups and ensure that each field contains atomic values (one value per cell). 2.1 Each row is unique, and there are no repeating columns or groups. 3. Ex: StudentID Name Course ------------------------------ 1 John Math 1 John Physics 2 Jane Math 2 Jane Chemistry 2 Jane Physics 3. Second Normal Form (2NF): 1. Achieve 1NF and remove partial dependencies (i.e., non-key attributes must depend on the entire primary key). 2. Applicable only if the table has a composite primary k 3. Ex:-- Students Table (No partial dependencies) StudentID Name ------------------- 1 John 2 Jane -- Enrollment Table (StudentID and Course form composite PK) StudentID Course -------------------- 1 Math 1 Physics 2 Math 2 Chemistry 2 Physics 4. Third Normal Form (3NF): 1. Achieve 2NF and remove transitive dependencies (i.e., non-key attributes must depend only on the primary key and not on other non-key attributes). Ex: -- Students Table StudentID Name ------------------- 1 John 2 Jane -- Enrollment Table StudentID Course ------------------- 1 Math 1 Physics 2 Math 2 Chemistry 2 Physics -- Courses Table (Instructor information is stored separately) Course Instructor ------------------------ Math Dr. Smith Physics Dr. Brown Chemistry Dr. White

SQL Question: Be able to write simple SQL statements given a question asked about the data (e.g. How many paint colors use the Raw Umber colorant?). You will be given the API for SQL commands that you may need to use but know the commands.

1. How many paint colors use the Raw Umber colorant? SELECT COUNT(DISTINCT Color.ColorName) AS NumPaintColors FROM Formula JOIN Color ON Formula.ColorCode = Color.ColorCode JOIN Colorant ON Formula.ColorantCode = Colorant.ColorantCode WHERE Colorant.ColorantName = 'Raw Umber'; 2. List all the employees who work in the Marketing department. SELECT EMP_LNAME, EMP_FNAME FROM EMPLOYEES JOIN DEPARTMENTS ON EMPLOYEES.DEPT_ID = DEPARTMENTS.DEPT_ID WHERE DEPARTMENTS.DEPT_NAME = 'Marketing'; 3. Find all employees who are older than 30 years. SELECT EMP_LNAME, EMP_FNAME, EMP_AGE FROM EMPLOYEES WHERE EMP_AGE > 30; 4. What is the total number of orders placed by customer 'John Smith'? SELECT COUNT(*) AS TotalOrders FROM ORDERS JOIN CUSTOMERS ON ORDERS.CUST_ID = CUSTOMERS.CUST_ID WHERE CUSTOMERS.CUST_LNAME = 'Smith' AND CUSTOMERS.CUST_FNAME = 'John'; 5. Find all employees who work in departments that have more than 5 employees. SELECT DEPARTMENTS.DEPT_NAME, COUNT(EMPLOYEES.EMP_ID) AS NumEmployees FROM EMPLOYEES JOIN DEPARTMENTS ON EMPLOYEES.DEPT_ID = DEPARTMENTS.DEPT_ID GROUP BY DEPARTMENTS.DEPT_NAME HAVING COUNT(EMPLOYEES.EMP_ID) > 5; 6. Get a list of all distinct colorants used in formulas. SELECT DISTINCT Colorant.ColorantName FROM Formula JOIN Colorant ON Formula.ColorantCode = Colorant.ColorantCode; 7. Retrieve all orders placed between January 1, 2023, and March 1, 2023. SELECT * FROM ORDERS WHERE ORDER_DATE BETWEEN '2023-01-01' AND '2023-03-01'; 8. Find the total amount of all sales for the current year. SELECT SUM(ORDER_TOTAL) AS TotalSales FROM ORDERS WHERE YEAR(ORDER_DATE) = YEAR(CURRENT_DATE); 9. How many different departments exist in the company? SELECT COUNT(DISTINCT DEPT_NAME) AS NumDepartments FROM DEPARTMENTS;

Design Question: Determine entities and attributes

1. Identify Entities: 1. Entities are typically nouns, representing objects or concepts. 2. Example: Student, Course, Instructor, Department. 2. Define Attributes: 1. Attributes describe the details of each entity. 2. Example for Student: 2.1 StudentID (PK) 2.2 FirstName, LastName, DateOfBirth, Email 3. Establish Relationships: 1. Determine how entities relate (1:1, 1, M). 2. Example: A Student enrolls in many Courses (M). 4. Examples of Entities: 1. Student: StudentID, FirstName, LastName, Email 2. Course: CourseID, CourseName, Credits 3. Instructor: InstructorID, FirstName, LastName, HireDate 5. Relationships: 1. Student ↔ Course: Many-to-Many (M) 2. Course ↔ Instructor: One-to-One (1:1) 3. Department ↔ Instructor: One-to-Many (1)

Design Question: Identify relationships and remove M:N relationships

1. Identify Relationships: 1. Analyze the relationships between entities (One-to-One 1:1, One-to-Many 1:M, Many-to-Many M:N). 2. Example: Student ↔ Course (M). 2. Convert M to Two 1 Relationships: 1. To remove a Many-to-Many relationship, create a junction (bridge) table. 2. The junction table will have two 1relationships connecting the related entities. 3. Example: 1. Original M Relationship: Student ↔ Course (M): A student can enroll in many courses, and a course can have many students. Solution: Create an Enrollment table to convert the M relationship into two 1 relationships. Student ↔ Enrollment (1) Course ↔ Enrollment (1) Enrollment Table: 1. EnrollmentID (Primary Key) 2. StudentID (Foreign Key to Student) 3. CourseID (Foreign Key to Course) 4. Final Relationships: 1. Student ↔ Enrollment ↔ Course (Two 1relationships via Enrollment). Example Structure: Student (1) ↔ Enrollment (M:1) ↔ Course

SQL Question Part2: Explain what will result from an SQL statement (e.g. select * from EMPLOYEES where EMP_LNAME = 'Johnson'). Left Outer Join, Right Outer Join

1. Left outer join function: The LEFT OUTER JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, the result is NULL for columns from the right table. Ex: SELECT * FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID; Result: This will return all rows from the EMPLOYEES table, along with matching rows from the DEPARTMENTS table. If there is no matching department for an employee, the DEPARTMENTS fields will be NULL for that employee. 2. Right outer join function: The RIGHT OUTER JOIN returns all rows from the right table and the matching rows from the left table. If there is no match, the result is NULL for columns from the left table. Ex: SELECT * FROM EMPLOYEES E RIGHT OUTER JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID; Result: This will return all rows from the DEPARTMENTS table, along with matching rows from the EMPLOYEES table. If there is no matching employee for a department, the EMPLOYEES fields will be NULL for that department. ---- 1. SELECT retrieves data. 2. AND requires all conditions to be true. 3. OR requires any condition to be true. 3. WHERE filters results based on a condition. 4. NATURAL JOIN automatically joins tables on matching column names. 5. LEFT OUTER JOIN returns all rows from the left table, even if there's no match in the right table. 6. RIGHT OUTER JOIN returns all rows from the right table, even if there's no match in the left table.

SQL Question: Write the resulting tables from natural join, left outer join, and right outer join

1. Natural join: SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS; 2. Left outer join: SELECT * FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID; 3. Right outer join: SELECT * FROM EMPLOYEES E RIGHT OUTER JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID;

SQL Question Part1: Explain what will result from an SQL statement (e.g. select * from EMPLOYEES where EMP_LNAME = 'Johnson'). Select, And, Or, Where, Natural Join.

1. Select function: The SELECT statement is used to query data from a database table. Ex: SELECT * FROM EMPLOYEES; Result: his will return all columns (* means all) and all rows from the EMPLOYEES table 2. AND function: the AND operator is used to combine multiple conditions in a WHERE clause. All conditions must be TRUE for the row to be included in the result. Ex: SELECT * FROM EMPLOYEES WHERE EMP_LNAME = 'Johnson' AND EMP_AGE > 30; Result: This will return all rows from the EMPLOYEES table where the last name is "Johnson" and the age is greater than 30. Both conditions must be satisfied. 3. OR function: The OR operator is used to combine multiple conditions in a WHERE clause. If any condition is TRUE, the row will be included in the result. Ex: SELECT * FROM EMPLOYEES WHERE EMP_LNAME = 'Johnson' OR EMP_AGE > 30; Result: This will return all rows from the EMPLOYEES table where either the last name is "Johnson" or the age is greater than 30. Only one of the conditions must be met. 4. WHERE function: The WHERE clause filters rows based on a specified condition. Only rows that satisfy the condition will be returned. Ex: SELECT * FROM EMPLOYEES WHERE EMP_LNAME = 'Johnson'; Result: This will return all rows from the EMPLOYEES table where the last name is "Johnson". 5. Natural Function: The NATURAL JOIN operation automatically joins two tables based on columns that have the same names and data types in both tables. It eliminates the need for specifying a JOIN condition explicitly. Ex: SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS; Result: This will return rows where the column names in both tables match. The join happens automatically based on these matching column names.