Database Design
Ch05-Ch06 – Activities and Homework
Name: Click or tap here to enter text.
Date: Click or tap here to enter text.
Chapter 05 REVIEW (5 points)
Respond to the following Chapter 5 review questions (you may have discussed some of these questions in class so be sure to include ideas and information you gained from the class discussion).
(1pt) What primary key characteristics are considered desirable? Explain why each characteristic is considered desirable.
Click or tap here to enter text.
(1pt) Under what circumstances are composite primary keys appropriate?
Click or tap here to enter text.
(1pt) What is a surrogate primary key, and when would you use one?
Click or tap here to enter text.
(2pts) When implementing a 1:1 relationship, for example between table A and table B, where there must always be an entry in table A, but a related entry in table B is optional, respond to the follow.
Where should the FK be placed? In table A or table B?
Click or tap here to enter text.
Should the foreign key be mandatory or optional in your table chosen above?
Click or tap here to enter text.
Chapter 05 PROBLEMS (15 points)
(9 pts total) Given the following attributes in an Employee Master file, respond to questions a through d below. In your response, consider all attribute options and composite attribute options.
Employee Master File Attributes and Properties
Attribute Name
Attribute Property
Employee_Number
Integer (6)
Employee_SSN
Integer (9)
Employee_Drivers_License
String (12)
Employee_First_Name
String (25)
Employee_Last_Name
String (25)
Employee_Birth_Date
Date
Employee_Address
String (25)
Employee_City
String (25)
Employee_State
String (2)
Employee_Zip
String (10)
Employee_Date_Of_Hire
Date
Employee_Job_Title
String (25)
Employee_Salary
Number (9,2)
(2pts) What is the best attribute for the primary key and why?
Click or tap here to enter text.
(3pts) What attributes could be a natural key and why (list 3 possibilities, both single attributes and composite attribute)?
Click or tap here to enter text.
(2pts) What two or more attributes might be used as a composite key and why? What possible problems could these keys create?
Click or tap here to enter text.
(2pts) Could any of these attributes be considered a Surrogate Primary Key and why?
Click or tap here to enter text.
(4 pts total) You are a Database Designer and you need to create a table to store vehicle information for standard passenger cars. Respond to the following questions:
(2pts) What information would be best for a primary key and why?
Click or tap here to enter text.
(2pts) What information might be used for an alternative primary key (single attribute or composite key) if your response in question 2a is not available?
Click or tap here to enter text.
(2pts) You are a Database Designer for a rare books company and you need to create a table to store all the unique rare books the company has in stock. Each copy of a book must be uniquely identified by book title, book author, and book printing date. What would you use for a primary key and why? (Note: the company might have multiple copies of the same book from the same printing date, but each copy must still be uniquely identified).
Click or tap here to enter text.
Chapter 06 REVIEW (10 points)
(2pts) When is a table in 3NF?
Click or tap here to enter text.
(2pts) When is a table in BCNF?
Click or tap here to enter text.
Given the dependency diagram shown below in Figure Q6.6, answer items 3a-3c:
a. (2pts) Identify and describe each of the indicated dependencies.
Determinant
(single or composite attributes)
Determines which attribute(s)…
Type of Dependency
(full, partial, transitive)
Explain why you chose the dependency type
C1
C2
Partial
Because C1 is part of a composite key and …
b. (2pts) Using the Visio starter file found in canvas, modify this dependency diagram for this database, where the tables in 2NF, but not yet in 3NF, showing the dependency diagrams for each table. Copy & paste the image from your VISIO diagram into this assignment here:
Paste dependency diagram(s) here.
NEXT, describe the dependency diagram(s) by filling in the following table:
Table
#
Normal
Form
Relational Schema
(page 115)
Primary
Key(s)
Foreign
Key(s)
Dependencies
(e.g. Y1 > Y3, Y4)
Dependency Type for each
Listed Dependency
c. (2pts) Using Visio, modify the dependency diagram for a database whose tables are at least in 3NF, showing the dependency diagrams for each table. Copy & paste the image from your VISIO diagram into this assignment here:
Paste dependency diagram(s) here..
NEXT, describe the dependency diagram(s) by filling in the following table:
Table
#
Normal
Form
Relational Schema
(page 115)
Primary
Key(s)
Foreign
Key(s)
Dependencies
(e.g. Y1 > Y3, Y4)
Dependency Type for each
Listed Dependency
Chapter 06 PROBLEMS (20 points)
(See the text for details for each question)
(3pts) Using VISIO and the attributes given in the ERD in the figure below, convert the ERD into a 3NF dependency diagram.
Hints:
First draw 2 dependency diagrams, 1 per entity (rectangles next to each other, 1 per attribute). Use the Visio starter file found in Canvas
Next, for each of the 2 dependency diagrams you drew, draw the fully functional dependency lines along the top of your dependency diagrams.
Next, identify and draw partial dependency lines if they exist, along the bottom of the dependency diagrams. These exist only when:
There is a composite key in an entity
AND when part of the composite key (1 of the attributes of a composite PK) determines 1 or more attributes.
Next, identify and draw transitive dependency lines if they exist, along the bottom of the dependency diagrams.
These exist where one or more attributes can be determined by an attribute that is not part of the primary key.
Paste your dependency Diagrams in 3NF here.
(11 points total) Using the INVOICE table structure shown below…
(3pts) Draw its dependency diagram in 1NF which identifies all dependencies, including FULL, partial and transitive dependencies, and 2) write the relational schema which corresponds to the dependency diagram.
Hint #1: INVOICE_NUM and InvLines_LineNum together form a composite primary key and determine all of the other attributes.
First draw the diagram (rectangles next to each other, 1 per attribute). Use the Visio starter file found in Canvas.
Next, draw the fully functional dependency lines along the top of your diagrams.
Next, identify and draw partial dependency lines if they exist, along the bottom of the dependency diagram. These exist only when:
There is a composite key in an entity
AND when part of the composite key (1 of the attributes of a composite PK) determines 1 or more attributes.
Next, identify and draw transitive dependency lines if they exist, along the bottom of the dependency diagrams.
These exist where one or more attributes can be determined by an attribute that is not part of the primary key.
Dependency Diagram with all dependencies labeled (include all full, partial, transitive, if any)
Relational Schema (page 115)
(3pts) Using Visio redraw the dependency diagrams with all partial dependencies removed so they are in at least 2NF (but still including any remaining transitive dependencies). Paste the redrawn image below. Next, 1) identify the normal forms for each table structure created and 2) write the new resulting relational schemas. Record your respond below.
Dependency Diagram with all dependencies (include all full and transitive, if any)
Relational Schema
(3pts) Using Visio redraw the dependency diagrams with all transitive dependencies removed so they are in at least 3NF. Paste the redrawn image below. Next, 1) identify the normal forms for each table structure created and 2) write the new resulting relational schemas. Record your respond below.
Dependency Diagram with all dependencies (include all full, partial, transitive, if any)
Relational Schema
(2pts) Using MySQL Workbench Designer or Visio, draw the Crow’s Foot ERD of the 3NF dependency diagrams and paste the image here. Be sure to indicate appropriate relationship strength and cardinality.
Click or tap here to paste your ERD.
(6 points total) Use VISIO to modify the initial dependency diagram shown below. Use the Visio starter file found in Canvas
(3pts) Remove all partial dependencies so you can now 1) draw the new dependency diagrams which 1.a) still must include remaining transitive dependencies (if any) and 1.b) also identify the normal forms for each table structure you created with a textbook next to the table structure.
Dependency Diagrams with all dependencies (include all full, partial, transitive, if any)
(3pts) Remove all transitive dependencies so you can now, 1) draw the new dependency diagrams which identify the normal forms for each table structure you created.
Dependency Diagrams with all dependencies (include all full, partial, transitive, if any)
Basic SQL Query Problems (10 points – 2 points per problem)
Basic SQL Primer:
The SELECT statement is used to return data from (or query) a database. The basic SELECT statement has five primary clauses, SELECT, FROM, WHERE, ORDER BY and AS. All SQL statements end with a semicolon.
SELECT – Use to select the specific columns (attributes) from a table. An asterisk (*) is used to return all columns.
FROM – Used to determine the table (or tables) in the query.
WHERE – Used to limit the number of rows (or records) returned in the query.
ORDER BY – Used to sort the query result by a given column (or columns).
AS – Used to provide an alias for part of the query, such as changing the column name of database table in the query result.
For example, the follow SQL query will return the employee number, employee last name and employee first name from the employee table where the job code is equal to 502 and their hire date is before January 1, 2000. It will change the column headers to “Employee Number”, “Last Name”, “First Name”. It will also sort the list by the employee’s last name.
SELECT EMP_NUM AS “Employee Number”,
EMP_LNAME AS “Last Name”,
EMP_FNAME AS “First Name”
FROM employee
WHERE JOB_CODE = 502 AND EMP_HIREDATE < '2000-01-01'
ORDER BY EMP_LNAME;
Using this basic understanding of SQL SELECT statements, complete the following problems following instructions below:
Important Notes:
For each problem,
Create a NEW SQL SCRIPT TAB
SAVE the script tab by naming it: [your name] Problem [problem#]. For example: Justin Problem 1
Clear the OUTPUT at the bottom.
After developing the correct SQL statement to properly solve the problem statement, get a screenshot that contains ALL of the following and paste it below the problem:
TAB NAME
SQL statements
RESULT GRID
OUTPUT AREA
The screenshot at the right shows an example of what you should copy & paste
Create a ch06_constructco database/schema in MySQL Workbench and select the database.
Open and run the Ch06_ConstructCo_MySQL.sql script from the Student Files.
Use the ch06_constructco database/schema for problems 1-5.
1. (2pts) Write the SQL SELECT statement to select all columns (attributes) and all rows FROM the employee table. A correct result will return 18 rows and look like the following:
MySQL Workbench Screen Result
2. (2pts) Write the SQL SELECT statement to select the EMP_NUM, EMP_LNAME, EMP_FNAME and EMP_HIREDATE FROM the employee table WHERE the EMP_HIREDATE is after January 1, 2000. A correct result will return 3 rows and look like the following:
MySQL Workbench Screen Result
3. (2pts) Write the SQL SELECT statement to select the JOB_DESCRIPTION and JOB_CHG_HOUR FROM the job table WHERE the JOB_CHG_HOUR is grater than $50.00 and display the result in alphabetical order by JOB_DESCRIPTION using the ORDER BY clause. A correct result will return 5 rows and look like the following:
MySQL Workbench Screen Result
4. (2pts) Write the SQL SELECT statement to select the ASSIGN_NUM, PROJ_NUM and ASSIGN_HOURS FROM the assignment table WHERE the PROJ_NUM is equal to 15 and the ASSIGN_HOURS > 2. A correct result will return 5 rows and look like the following:
MySQL Workbench Screen Result
5. (2pts) Write the SQL SELECT statement to select the EMP_FNAME and EMP_LNAME FROM the employee table WHERE the JOB_CODE is equal to 501. Use the AS clause to rename the column headers to “First Name” and “Last Name”. A correct result will return 5 rows and look like the following:
MySQL Workbench Screen Result
