PROJECT I - SQL The Urban Bike Company database keeps track of the company’s emp
ID: 3910078 • Letter: P
Question
PROJECT I - SQL
The Urban Bike Company database keeps track of the company’s employee, department, customer and orders for its inventory. The system must update the available quantity on hand to reflect that the bike has been sold.
The company has many department. Each department has a unique number, a unique name. One department may have multiple locations.
We store each employee's name, employee identification number, social security number, address, salary, sex and birthday. An employee must be either a full-time or part-time employee.
We want to keep track of orders placed by customers. We keep each customer number, name, address.
Each order has order ID and order day with default current date.
Each product has SKU, name and price and quantity.
Business rules.
One customer may or may not place many orders.
One order must be placed by one and only one customer.
One order must contain one or more bikes.
One bike may or may not be contained in many orders.
One employee may or may not process many orders.
One order must be processed by one and only one employee.
One employee must be either a full-time or part-time employee
One full-time or part-time employee must be an employee.
One department must have at least 3 or more employees.
One employee must work for one and only one department.
1. Identify entity types and relationship types. Fill out the following relationship matrix.
Employee
Department
Customer
Order
Bike
Full-Time
Part-Time
Employee
has
Is processed
is
is
Department
Works for
Customer
Is placed
Order
processes
places
Is contained
Bike
contains
Full-Time
is
Part-Time
is
2. Draw an EER diagram includes
1) entity types, 2) relationship types, 3) keys, 4) cardinality constraints (must show participation)
Database Logical Design: Map the ER diagram to a relational database schema indicating the relation name, primary key and foreign key. Add appropriate additional attributes by yourself.
Department
DepartmentId (PK)
DepartmentName
DepartmentLocation
DepartmentId(PK) (FK)
Location (PK)
Employee
EmployeeId (PK)
FirstName
Last Name
SSN
StreetNum
StreetName
City
State
ZipCode
Sex
BirthYear
DepartmentId(FK)
Order
OrderID (PK)
OrderDate
EmployeeId (FK)
CustomerId (FK)
Customer
CustomerID (PK)
FirstName
LastName
StreetNum
StreetName
City
State
ZipCode
OrderForm
BikeId (PK) (FK)
OrderId (PK) (FK)
Bike
SKU (PK)
Name
Price
Part-TimeEmployee
EmployeeId (PK) (FK)
HoursPerWeek
Full-TimeEmployee
EmployeeId (PK) (FK)
Bonus
Establish join paths for the above relational database using the referential integrity by drawing arrow lines between the above tables. Indicate all the foreign keys (FK).
F.K. -> P.K.
Full-TimeEmployee.EmployeeId -> Employee.EmployeeId
Part-TimeEmployee.EmployeeId -> Employee.EmployeeId
DepartmentLocation.DepartmentId -> Department.DepartmentId
Employee.DepartmentId -> Department.DepartmentId
Order.EmployeeId -> Employee.EmployeeId
Order.CustomerId -> Customer.CustomerId
OrderForm.BikeId -> Bike.BikeId
OrderForm.OrderId -> Order.OrderId
Functional Dependency Analysis for Normalization
Department.DepartmentId -> DepartmentName (Full Dependency)
Employee.EmployeeId -> FirstName, LastName, SSN, StreetNum, StreetName, City, Sex, BirthYear, BirthMonth, BirthDay, DepartmentID (Full Dependency)
City.ZipCode, -> City.State, Employee.City, Street (Transitive Dependency)
OrderId -> OrderDate, EmployeeId, CustomerId (Full Dependency)
Customer.CustomerId -> FirstName, LastName, StreetNum, StreetName, City (Full Dependency)
Customer.ZipCode -> Customer.State, Customer.City, StreetName (Transitive Dependency)
SKU -> Name, Price (Full Dependncy)
Part-TimeEmployee.EmployeeId -> HoursPerWeek (Full Dependency)
Full-TimeEmployee.EmplyoeeId -> Bonus (Full Dependency)
Normalized tables in the relational schema (Indicate each table’s Normal form)
Table Name
1NF
2NF
3NF
Department
X
X
X
DepartmentLocation
X
X
X
Employee
X
X
Transitive
Order
X
X
X
Customer
X
X
Transitive
OrderForm
X
X
X
Bike
X
X
X
Part-TimeEmployee
X
X
X
Full-TimeEmployee
X
X
X
Project Part II
Oracle Database Development
Project II continues developing the database based on your Project 1. Create the database based on the logical design in Project 1 using Oracle. Run SQL queries to test the database in your Oracle account.
Project 2 would typically go through the following phases:
1. Create the Company database with five constraints in the six appropriate tables and load data into the database via ORACLE SQL*PLUS.
Submission: Printout of SQL DDL script (Database creation script), database structure (DESC TableName) and database instance (SELECT * FROM TableName)
2. Test your Company relational database via ORACLE SQL*PLUS, which includes SQL statements, SQL solutions and output and save them in the MS word file.
2.1 Your SQL DML statements must include insert, delete, update and view
2.2 Your SELECT statements must include join tables, subqueries, Group by …. Having and function statements.
Submission: Printout of Question, corresponding SQL statement and corresponding output for each query. Finalized project table of content, Combined Project 1 and Project 2 in Project 2 online by group
Note: One Select statement per team member
One SQL DML statement per team member
Here are the additional tables provided by the instructor:
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
DROP TABLE DEPT;
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
ALTER TABLE emp
ADD CONSTRAINT emp_empno_pk
PRIMARY KEY (empno);
ALTER TABLE emp ADD
constraint emp_deptno_fk Foreign key (deptno) references dept (deptno);
ALTER TABLE dept
ADD CONSTRAINT dept_deptno_pk
PRIMARY KEY (deptno);
ALTER TABLE dept
ADD CONSTRAINT dept_deptno_ck check (deptno between 10 and 99);
ALTER TABLE dept
ADD constraint dept_dname_uk unique (dname);
Employee
Department
Customer
Order
Bike
Full-Time
Part-Time
Employee
has
Is processed
is
is
Department
Works for
Customer
Is placed
Order
processes
places
Is contained
Bike
contains
Full-Time
is
Part-Time
is
Explanation / Answer
create table DEPARTMENT (
DEPARTMENTID NUMBER not null,
DEPARTMENTNAME VARCHAR2(100),
CONSTRAINT DEPARTMENT_pk PRIMARY KEY (DEPARTMENTID)
);
create table DepartmentLocation (
Location NUMBER not null,
DEPARTMENTID NUMBER,
CONSTRAINT DepartmentLocation_pk PRIMARY KEY (Location),
CONSTRAINT DepartmentLocation_fk FOREIGN KEY (DEPARTMENTID) REFERENCES DEPARTMENT(DEPARTMENTID)
);
create table Employee (
EmployeeId NUMBER not null,
FirstName VARCHAR2(50),
LastName VARCHAR2(50),
SSN VARCHAR2(50),
StreetNum NUMBER,
StreetName VARCHAR2(100),
City VARCHAR2(50),
State VARCHAR2(50),
ZipCode VARCHAR2(10),
Sex VARCHAR2(5),
BirthYear NUMBER,
DEPARTMENTID NUMBER,
CONSTRAINT Employee_pk PRIMARY KEY (EmployeeId),
CONSTRAINT Employee_fk FOREIGN KEY (DEPARTMENTID) REFERENCES DEPARTMENT(DEPARTMENTID)
);
create table Customer (
CustomerID NUMBER not null,
FirstName VARCHAR2(50),
LastName VARCHAR2(50),
StreetNum NUMBER,
StreetName VARCHAR2(100),
City VARCHAR2(50),
State VARCHAR2(50),
ZipCode VARCHAR2(10),
CONSTRAINT Customer_pk PRIMARY KEY (CustomerID)
);
create table Order_u (
OrderID NUMBER not null,
OrderDate DATE,
EmployeeId NUMBER,
CustomerId NUMBER,
CONSTRAINT Order_pk PRIMARY KEY (OrderID),
CONSTRAINT Order1_fk FOREIGN KEY (EmployeeId) REFERENCES Employee(EmployeeId),
CONSTRAINT Order2_fk FOREIGN KEY (CustomerId) REFERENCES Customer(CustomerId)
);
create table OrderForm (
BikeId NUMBER not null,
OrderId NUMBER not null,
CONSTRAINT OrderForm_pk PRIMARY KEY (BikeId, OrderId),
CONSTRAINT OrderForm_fk FOREIGN KEY (BikeId) REFERENCES Bike(SKU),
CONSTRAINT OrderForm2_fk FOREIGN KEY (OrderId) REFERENCES Order_u(OrderID)
);
create table Bike (
SKU NUMBER not null,
Name VARCHAR(50),
Price NUMBER,
CONSTRAINT Bike_pk PRIMARY KEY (SKU)
);
create table PartTimeEmployee (
EmployeeId NUMBER not null,
HoursPerWeek NUMBER,
CONSTRAINT PartTimeEmployee_pk PRIMARY KEY (EmployeeId),
CONSTRAINT PartTimeEmployee_fk FOREIGN KEY (EmployeeId) REFERENCES Employee(EmployeeId)
);
create table FullTimeEmployee (
EmployeeId NUMBER not null,
Bonus NUMBER,
CONSTRAINT PartTimeEmployee_pk PRIMARY KEY (EmployeeId),
CONSTRAINT PartTimeEmployee_fk FOREIGN KEY (EmployeeId) REFERENCES Employee(EmployeeId)
);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.