Database Reference
In-Depth Information
SQL ON DELETE clause
SQL ON UPDATE clause
SQL/Persistent Stored Modules (SQL/PSM)
SQL script
SQL script file
SQL TRUNCATE TABLE statement
SQL UPDATE statement
SQL view
stored function
stored procedure
systems analysis and design
systems development life cycle (SDLC)
transaction control language (TCL)
Transact-SQL (T-SQL)
trigger
UNIQUE constraint
user-defined function
7.1
What does DDL stand for? List the SQL DDL statements.
7.2
What does DML stand for? List the SQL DML statements.
7.3
Explain the meaning of the following expression: IDENTITY (4000, 5).
For this set of Review Questions, we will create and use a database for the
Review Wedgewood Pacific Corporation (WPC) that is similar to the Microsoft
Access database we created and used in Chapters 1 and 2. Founded in 1957 in
Seattle, Washington, WPC has grown into an internationally recognized organization.
The company is located in two buildings. One building houses the Administration,
Accounting, Finance, and human Resources departments, and the second houses the
Production, Marketing, and Information Systems departments. The company database
contains data about employees; departments; projects; assets, such as computer
equipment; and other aspects of company operations.
The database will be named WPC and will contain the following four tables:
DEPARTMENT (
DepartmentName
, BudgetCode, OfficeNumber, Phone)
EMPLOYEE (
EmployeeNumber
, FirstName, LastName,
Department
, Phone, Email)
PROJECT (
ProjectID
, Name,
Department
, MaxHours, StartDate, EndDate)
ASSIGNMENT (
ProjectID
,
EmployeeNumber
, HoursWorked)
EmployeeNumber is a surrogate key that starts at 1 and increments by 1.
ProjectID is a surrogate key that starts at 1000 and increases by 100. DepartmentName
is the text name of the department and is therefore not a surrogate key.
The WPC database has the following referential integrity constraints:
Department in EMPLOYEE must exist in Department in DEPARTMENT
Department in PROJECT must exist in Department in DEPARTMENT
ProjectID in ASSIGNMENT must exist in ProjectID in PROJECT
EmployeeNumber in ASSIGNMENT must exist in EmployeeNumber in EMPLOYEE
The relationship from EMPLOyEE to ASSIGNMENT is 1:N, M-O and the
relationship from PROJECT to ASSIGNMENT is 1:N, M-O.
The database also has the following business rules:
●
If an EMPLOYEE row is to be deleted and that row is connected to any
ASSIGNMENT, the EMPLOYEE row deletion will be disallowed.
●
If a PROJECT row is deleted, then all the ASSIGNMENT rows that are connected to
the deleted PROJECT row will also be deleted.
Search WWH ::
Custom Search