A Guide to SQL
9th Edition
ISBN: 9781111527273
Author: Philip J. Pratt
Publisher: Course Technology Ptr
expand_more
expand_more
format_list_bulleted
Question
Chapter 2, Problem 1CAT
Program Plan Intro
Normalization:
- Normalization refers to the method of representing a
database in the terms of relations, which are in the standard normal forms. - It is organized in a table manner to reduce data redundancy and data dependency.
First Normal Form (1NF):
A table or a relation is said to be in first normal form if and only if the domain of each attribute consists of only unique values and the value of each attribute consists of only a single value.
Second Normal Form (2NF):
A table or a relation is said to be in second normal form if and only if it is in first normal form and no non-key attribute of the table is dependent on only a portion of the primary key.
Third Normal Form (3NF):
A table or a relation is said to be in third normal form if and only if it is in second normal form and if the candidate keys are its only determinants.
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
The marketing team wants to celebrate the success of StayWell with a party. The team wants a table with the names of all residents and owners combined into single column named PARTICIPANT. You need to combine this information from the tables and send it back to the team.
You do not need to create a new table in the database schema.
Task
Return a list of all the residents and owners
Implement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the diagram below
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 these…
In anticipation of a change to the database, you are asked to provide a list of all products that have 'Man-Made' or 'Man Made' in the the composition text. (HInt: It is easiest to use LIKE in this instance.) In addition, you are requested to replace either 'Man-Made' or 'Man Made' with the word 'Synthetic'. Only include ProductIDs between 1050 and 1060. Show the following in your answer: ProductID, ProductName, Composition, and the modified composition field as 'NewComposition'.
(HINT: This problem is a little tricky because you need to replace both 'Man-Made' and 'Man Made'. The way to do this is to use a replace within a replace.)
Chapter 2 Solutions
A Guide to SQL
Ch. 2 - Prob. 1RQCh. 2 - What is an attribute?Ch. 2 - What is a relationship? What is a one-to-many...Ch. 2 - Prob. 4RQCh. 2 - What is a relation?Ch. 2 - Prob. 6RQCh. 2 - Prob. 7RQCh. 2 - How do you qualify the name of a field, and when...Ch. 2 - Prob. 9RQCh. 2 - What is a primary key? What is the primary key for...
Ch. 2 - Prob. 11RQCh. 2 - Prob. 12RQCh. 2 - Define second normal form. What types of problems...Ch. 2 - Define third normal form. What types of problems...Ch. 2 - Prob. 15RQCh. 2 - Prob. 16RQCh. 2 - List the changes you would need to make to your...Ch. 2 - Prob. 1TDCh. 2 - Prob. 2TDCh. 2 - Prob. 3TDCh. 2 - Prob. 4TDCh. 2 - Prob. 5TDCh. 2 - Prob. 1CATCh. 2 - Identify the functional dependencies in the...Ch. 2 - Prob. 3CATCh. 2 - Determine the functional dependencies that exist...Ch. 2 - Prob. 2SCGCh. 2 - Prob. 3SCG
Knowledge Booster
Similar questions
- 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…arrow_forwardImplement 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…arrow_forwardStayWell also rents out properties on a weekly basis to students attending summer school in the Seattle area. Design a database to meet the following requirements, using the shorthand representation and a diagram of your choice. For each student renter, list his or her number, first name, middle initial, last name, address, city, state, postal code, telephone number, and e-mail address. For each property, list the office number, property address, city, state, postal code, square footage, number of bedrooms, number of floors, maximum number of persons that can sleep in the unit, and the base weekly rate. For each rental agreement, list the renter number, first name, middle initial, last name, address, city, state, postal code, telephone number, start date of the rental, end date of the rental, and the weekly rental amount. The rental period is one or more weeks.arrow_forward
- Design a survey form of your own. Fill it out as it might have been completed during the database design for Henry Books. For any questions you have too little information to answer, make a reasonable guess. (Ray Henry, the owner of a bookstore chain named Henry Books, gathers and organizes information about branches, publishers, authors, and books. Each local branch of the bookstore has a number that uniquely identifies the branch. In addition, Ray tracks the branch’s name, location, and number of employees. Each publisher has a code that uniquely identifies the publisher. In addition, Ray tracks the publisher’s name and city. The only user of the Book database is Ray, but you do not want to treat the entire project as a single user view. Ray has provided you with all the reports the system must produce, and you will treat each report as a user view. Ray has given you the following requirements: User View 1 Requirements: For each publisher, list the publisher code, publisher name, and…arrow_forwardWhen working with subtype associations, it is important to discuss the many database table options that are available.Determine the layout that you wish to use, then discuss the concerns that are pertinent to it.arrow_forwardAssume you have a vehicle table with a surrogate key vehicle_id column and VIN (Vehicle Identification Number) column and some other columns. You want to designate a unique index on the vehicle_id and VIN columns because together they filter on the joining column with the highest cardinality (or degree of uniqueness). Write the syntax to add a unique index on the vehicle_id and VIN columns triggered by joins on the surrogate key column?arrow_forward
- Using the Henry Books database,update the paperback field for the book titled Black Houseto Y. You will insert 2 snips for this question. Insert a snip of the construct used to update the table: Insert your screen shot of the Books table here (be certain Black Housedisplays): Using the Henry Books database,add a new row to the AUTHORS table. The new author’s name is John Grisham. You will insert 2 snip s for this question. Insert a snip of the construct used to update the table: Insert your snip of the Authors table here showing the update: Using the Henry Books database,create a view named Paperback. It consists of the book code, book title, publisher name, and book price for every book that is available in paperback. You will insert 2 snips for this question. Insert a snip of the construct used to create this view: Run a query using this view and insert a snip of the resultset: Using the Henry Books database,write a stored procedure that…arrow_forwardYou are asked to create a database for a busing company. You need to keep track of the differ- ent buses, each having a plate number, make year, and number of seats. You also need to keep track of drivers that work in the company. The information kept on each driver should include the license number, age, and name. Finally, you need to keep information on which drivers are allowed to drive different buses. Drivers cannot drive any bus, but rather each driver is assigned with specific buses in the fleet he/she is allowed to drive. Accordingly, a driver may be allowed drive many different buses, and a bus can be driven by many different drivers. 2. Convert the ER diagram that is found in the next page to a logical relational data model. Submit in your file the relational model you arrived at (i.e. the list of tables, their columns, indicating their primary and foreign keys). Note: You are NOT asked to create an Access Database, but rather only provide the list of tables you would have in…arrow_forwardThe Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR_ID (CHAR(5)), MAINTENANCE_TYPE_ID (CHAR(5)) and MAINTENANCE_DUE (DATE) date for the operation. Create a new table named MAINTENANCES. The PRIMARY_KEY should be the combination of the three fields. The CAR_ID and MAINTENACNE_TYPE_ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys.arrow_forward
- Convert the following table to third normal form (3NF). In this table, StudentNum determines StudentName, NumCredits, AdvisorNum, and AdvisorName. Advisor Num determines AdvisorName. CourseNum determines Description. The combination of StudentNum and CourseNum determines Grade. STUDENT (StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName, CourseNum, Description, Grade). Identify the primary key for the original table and the primary keys for the resulting tables. Indicate the foreign keys with italic font. Give meaning names to the resulting tables.arrow_forwardYour task is to develop a database, in order to keep information on all sales at a motorcycle dealership. The database will include information on motorcycles, salespeople, sales, and customers. We assume for simplicity that the dealership sells only new motorcycles and that all motorcycles are produced by the same manufacturer. Each kind of motorcycle is called a model, and it has a unique model number. For each model, you want to store also its name, engine size in cubic centimeters (cc) which is an integer number, and the date when this particular model was introduced. For each salesperson, you want to store their employee ID number, first name, middle initial, last name, and the date when the employee was hired. For each customer, you want to store a customer ID number, first name, middle initial, last name, address information (city, state, and zipcode), phone number (given in standard US format), and e‐mail address. Assume that you do not have a zip code table available. Each…arrow_forwardYou are to design a Hospital database. So before creating the database, your task is to draw the ER diagram based on the following data collected and analyzed: A hospital will have a name, unique id, multiple helpline numbers (one for doctor's appointment, one for ambulance, one for bill information and one for other services' info) and locations. A hospital may have more than one location as it may have different branches and each of the locations will have a building no., street address, road no. and city name. A patient will have a name, unique ID, may have more than one phone number, age, height, weight and symptoms. A patient takes an appointment in a hospital and the appointment date is recorded. A patient may have an attendant. An attendant will have his/her name, contact numbers and relationship with the patient as a record in the database. Due to the covid situation, the hospital authorities will only allow 1 attendant with 1 patient.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr