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.

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.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

QUESTION 5 Consider the following relations in a University database: Student (SID, SName, Age, GPA) Course (CID, CName, Lecturer) Enrollment (SID, CID, Grade) SID is the primary key of Student, and CID is the primary key of Course (SID, CID) is the primary key of Enrollment, where SID is the foreign key referencing Student and CID is the foreign key referencing Course. CName is a unique field in the Course relation. Given the following SQL query: SELECT S.SName, E. Grade FROM Student S, Course C, Enrollment E WHERE S.SID = E.SID AND C.CID = E.CID AND C.CName = 'Database'; Show the initial query tree based on the conceptual evaluation strategy. Show the most efficient query tree after applying all the five steps of rule-based query tree optimization. (1) (2) Break up conjunctive selection into a cascade of selection operators; • Push down selection operators; . Convert cross-products into joins; Rearrange leaf nodes to execute the most restrictive selection operators first, Push down…
Indicate the changes (using the shorthand representation) that you would need to make to the original KimTay Pet Supplies database design to support the following requirements. There is no relationship between customers and sales reps. When a customer places an order, any sales rep can process the order and create the invoice. On the invoice, you need to identify both the customer placing the order and the sales rep responsible for the invoice. Draw an E-R diagram for the new design.
Implement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the following diagram: The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names: All lower case Underscore separator between root and suffix Foreign keys have the same name as referenced primary key Write CREATE TABLE and ALTER TABLE statements that: Implement the entity as a new phone table. Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables. Remove the existing phone column from the Sakila address table. Step 2 requires adding a foreign key constraint to an existing table. Ex: ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; Specify data types as follows: phone_id, phone_number, and country_code have data type INT. phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'. Apply…
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…