7. In the context of database design, consider the following scenario: There is database table that contains columns for employee ID, employee name, department, department location, and project details. Upon analyzing the table, you notice that there are multiple rows with the same employee name, department, and department location, but different project details. Based on this scenario, which of the following best describes the normalization process that should be applied to improve the database design? A) Split the table into two: one containing employee ID and employee name, and the other containing department, department location, and project details. B) Combine all columns into a single table to reduce redundancy. C) Normalize the database to the third normal form by creating separate tables for employee details, department details, and project details, and establish relationships between them. D) No action is required as the table already follows the normalization principles.

Discuss the benefits of using denormalization in a database design. What are the scenarios where denormalization is preferable?

Consider the unnormalized data presented in the STUDENT table below:a) Identify the data redundancies and give reasons why they create problems in schemas. STUNUM STUNAME TUTNUM TUTNAME COURSENUM COURSEDESC GRADE 00118536 John Brown  RC985 Rose Carter CS1001 CS1002 CS1003 Comp Research English Database design B A A 00118537 Marie Smith TB526 Tom Black CS9001 CS9002 CS1002 Calculus Javascript English A B C 00118538 Mary Cole PF587 Peter Finch  CS9002 CS1003 CS1001 Javascript Database design Comp Research C A A 00118539 Ian Dunkley SH231 Sarah Hardy CS9005 CS9006 CS1002 Information Systems Web Design English A B B 00118540 Bob Marley JH850 Jacqui Hart CS9002 CS1002 CS1003 JavaScript English Database Deign  A B C b) Which dependencies represent 1NF violations? Identify and eliminate 1NF violations. Present your table(s) after transformation into 1NF.c) Identify and remove any 2NF violations. Show the resulting schemas and tablesd) Identify and…

All of you must be familier with human resources system (employee database). Soplease design database for a human resources system that at least has 4 entitiesinvolved and for the 4 entities please do the following:a. Create the tables that conform to 3NF.b. Draw the Crow’s Foot ERD to reflect the dependency and relationshipdiagrams;c. For all entities/tables created please write the MASTER and TRANSACTIONdata accordingly!d. Use SQL commands to to do selection which involve 1 table, 2 tables, and 3tables.

The database used for this question is a very simple one with the following schema: (Primary keys are bold, foreign keys are underlined)   CUSTOMER (CustID, FirstName, LastName, City, Phone, Email) INVOICE (InvoiceNumber, CustID, Date) INVOICE_ITEM(InvoiceNumber, ItemNumber, Quantity) ITEM (ItemNumber, ItemName, UnitPrice)   When a customer makes a purchase, an invoice is created. The invoice may be for many items. For example, in a single purchase, a customer might buy 10 Back Scratchers, 4 Hair Removers and a Dog Lead. Provide relational algebra (NOT SQL) queries to find the following information. NOTE: You can use the symbols  s, P, etc., or the words’ PROJECT’, ‘RESTRICT’ etc. as you prefer.  You do not need to try to make efficient queries – just correct ones. Where you use a join, always show the join condition.   List the dates on which Homer Griffin made purchases. List the first and last names of customers who have bought “Back Scratcher” or “Hair Remover” List the first…

Question  A city college would like to maintain their academic information in a MySql database system.  The following are the interested information: Courses: Catalog # (M170 for instance) Credit Hours; Name of the Course (Database Design Fundamentals for instance) Course Descriptions Prerequisite Components (Lecture, or Laboratory for instance) Classes: Class # Class Name (usually the course name, M 170 for instance) Start Date End Date Meeting time (9:30 am ~ 12:15 pm, for instance) Days in a week (Monday) Term (Spring 2018, for instance) Location (TIE Building for instance) Room (UB 301, for instance) Instructor Online status (online, or in-person) Faculty: Employee Id (001880301, for instance) First Name Last Name Title (professor, for instance) Email Phone Office Location (TIE building, for instance) Room (UB303, for instance) Student: Student ID First Name Last Name Plan Sub Plan Advisor Current GPA Grade of each course The Term you got the grade for a course. The class…

2. The following tables form part of a database held in a Relational Database Management System: Employee (empID, fName, IName, address, DOB, sex, position, deptNo) Department (deptNo, deptName, mgrEmpID) Project (projNo, projName, deptNo) WorksOn (empID, projNo, hoursWorked) where Employee contains employee details and empID is the key. Department contains department details and deptNo is the key. mgrEmpID identifies the employee who is the manager of the department. There is only one manager for each department. Project contains details of the projects in each department and the key is projNo (no two departments can run the same project). and WorksOn contains details of the hours worked by employees on each project, and empID/projNo form the key. a) List all employees in alphabetical order of surname and within surname, first name. b) List all the details of employees who are female. c) List the names and addresses of all employees who are Managers. d) Produce a list of the names and…