Database Reference
In-Depth Information
The business sense of these rules is as follows:
●
If an EMPLOYEE row is deleted (e.g., if the employee is transferred), then someone
must take over that employee's assignments. Thus, the application needs someone to
reassign assignments before deleting the employee row.
●
If a PROJECT row is deleted, then the project has been canceled, and it is unneces-
sary to maintain records of assignments to that project.
The column characteristics for these tables are shown in Figures 1-26
(DEPARTMENT), 1-28 (EMPLOyEE), 2-30 (PROJECT), and 2-32 (ASSIGNMENT). The
data for these tables are shown in Figures 1-27 (DEPARTMENT), 1-29 (EMPLOyEE),
2-31 (PROJECT), and 2-33 (ASSIGNMENT).
If at all possible, you should run your SQL solutions to the following questions
against an actual database. Because we have already created this database in
Microsoft Access, you should use an SQL-oriented DBMS such as Microsoft SQL
Server 2012, Oracle Database 11
g
Release 2, or MySQL 5.6 in these exercises. Create
a database named
WPC,
and create a folder in your
My Documents
folder to save and
store the *.sql scripts containing the SQL statements that you are asked to create
in the remaining questions pertaining to the WPC database in this section and the
following Project Questions section.
●
For the SQL Server Management Studio, create a folder named
WPC-Database
in the
Projects
folder structure in your
My Documents
folder.
●
In the Oracle SQL Developer folder structure in your
My Documents
folder, create a
folder named
WPC-Database
.
●
For the SQL Workbench, create a folder named
WPC-Database
in the
Schemas
folder
in your
MyDocuments
folder.
If that is not possible, create a new Microsoft Access database named
WPC-CH07.accdb
, and use the SQL capabilities in these exercises. In all the exercises,
use the data types appropriate for the DBMS you are using.
Write and save an SQL script named
WPC-Create-Tables.sql
that includes the
answers to Review Questions 7.4 through 7.13. Use
SQL script commenting
(/* and */
symbols) to write your answers to Review Questions 7.7 and 7.9 as comments so that
they cannot be run! Test and run your SQL statements for Review Questions 7.4, 7.5,
7.6, and 7.8 only. After the tables are created, run your answers to Review Questions
7.10 through 7.13. Note that after these four statements have been run the table
structure is exactly the same as it was before you ran them.
7.4
Write a CREATE TABLE statement for the DEPARTMENT table.
7.5
Write a CREATE TABLE statement for the EMPLOYEE table. Email is required and is
an alternate key, and the default value of Department is Human Resources. Cascade
updates but not deletions from DEPARTMENT to EMPLOYEE.
7.6
Write a CREATE TABLE statement for PROJECT table. The default value for MaxHours
is 100. Cascade updates but not deletions from DEPARTMENT to EMPLOYEE.
7.7
Write a CREATE TABLE statement for the ASSIGNMENT table. Cascade only deletions
from PROJECT to ASSIGNMENT; do not cascade either deletions or updates from
EMPLOYEE to ASSIGNMENT.
7.8
Modify your answer to Review Question 7.7 to include the constraint that StartDate be
prior to EndDate.
7.9
Write an alternate SQL statement that modifies your answer to Review Question 7.7 to
make the relationship between EMPLOYEE and ASSIGNMENT a 1:1 relationship.
7.10
Write an ALTER statement to add the column AreaCode to EMPLOYEE. Assume that
AreaCode is not required.
Search WWH ::
Custom Search