Database Description:
The asset database is to
contain information about the physical assets of a typical business. The database is to store some brief
information about the employees of the company. The physical assets will be split into categories, which will
consist of tables holding relevant information for each category of
assets. These categories will be
software, hardware, furniture and office supplies. Hardware will be further divided into systems and system
components.
Employee Relation:
The Employee Relation is a table containing information about the
employees of the company. This
information is their first and last name, phone number, address, and the key of
the table being the employee id number. Employees can have one or more systems assigned to them.
Furniture Relation:
The Furniture Relation contains information about the companies
furniture including a brief description, the manufacturer, and the asset number
of the piece, which is the key. Each
piece is in a location, being a room of the building.
Supply Relation:
The
Supply Relation contains information about the office supplies of the
company. The supplies don’t have an
asset number, since it would not make sense to asset each pencil, stapler,
etc. They are grouped by manufacturer
and description, the keys, with a quantity listed for each.
Software Relation:
The
Software Relation contains information about different programs. It includes title, version, and quantity of
the title and version owned by the company.
The licenses are split up into individual licenses, which have a serial
number and can be loaded on a particular system. Title and version form the
key.
Hardware Relation:
The Hardware Relation contains
information about hardware owned by the company. This includes an asset number, a description, and a type (system
or device). A system has a speed and a
serial number and can have devices in it or attached to it, such as a monitor,
keyboard, mouse, hard drive, etc. The
devices have serial numbers and may or may not be part of a system. Devices that are not part of systems may be
network printers that are stand alone or spare devices that are in storage. Asset number is the key.
SQL Create Statements:
create table employees(
employeeid number(4,0) not null primary key,
fname varchar2(30) not null, /* first name */
lname varchar2(30) not null, /* last name */
address varchar2(30), /* home address */
city varchar2(30), /* city */
state char(2), /* two digit state abbreviation */
zip number(9,0) check(zip>0),/* zip with extension */
phone number(10,0)) /* phone without separation */;
create table devices(
assetno number(10,0) not null primary key,/* asset number */
devicename varchar2(30), /* description and type */
serialno varchar2(20)) /* serialno may have characters */;
create table systems(
assetno number(10,0) not null primary key,/* asset number */
description varchar2(40), /* brief description */
serialno varchar2(20), /* manufacturer serial number */
speed number(4,0)) /* processor speed first digit */
/* is class ie 5 = pentium */;
create table software(
title varchar2(30) not null, /* software title */
version number(5) not null, /* version.revision number */
numlicenses number(5,0), /* how many coppies in the company */
primary key(title, version));
create table indlicenses(
title varchar2(30) not null, /* title from software */
version number(5) not null, /* version from software */
serialno varchar2(20) not null,/* manufacturer serial number */
primary key (serialno),
foreign key (title,version) references software);
create table supplies(
description varchar2(30) not null,/* brief description */
manf varchar2(20) not null, /* supply manufacturer */
quantity number(10,0) check(quantity>=0),/* how many items in the company */
primary key (description, manf));
create table furniture(
description varchar2(30) not null,/* brief description */
manf varchar2(20), /* manufacturer */
assetno number(10,0) not null primary key,
location varchar2(20)) /* physical location of the furniture */;
create table assignedto (
employeeid number(4,0) not null references employees,/* employee assigned to */
assetno number(10,0) not null references systems,/* machine assigned */
primary key (assetno));
create table locatedon (
title varchar2(30) not null, /* title from software */
version number(5) not null, /* version from software */
serialno varchar2(20) not null,/* serial number from indvlicense */
assetno number(10,0) not null,/* asset number from systems */
primary key (serialno, assetno),
foreign key (title,version) references software);
create table partof(
systemno number(10,0), /* asset number of system */
device number(10,0)) /* asset number of device */;
create table hardware (
assetno number(10,0) not null primary key,/* asset number */
description varchar2(30), /* brief description of the asset */
type varchar2 (10)) /* type of the asset */;
create table assets (
description varchar2(30), /* brief description of the asset */
type varchar2 (10)) /* hardware, software, */
/* supply, furniture */;
This index is for employee lookups by first and last
name.
create index fullname on
employees(lname,fname);
This view shows who has what system assigned to them.
create view whohaswhat as
select fname, lname, description
from employees, systems, assignedto
where assignedto.employeeid = employees.employeeid and
systems.assetno = assignedto.assetno;
drop trigger employeephonedefault;
create trigger employeephonedefault
after update on employees
for each row
when(new.phone is NULL)
begin
update employees
set phone = (4105550000)/* + employeeid)*/
where(new.phone is NULL)
end;
run./
SQL Insert Statements:
insert into employees values
(101,'Fred','Frey','123 UMBC Way','Baltimore','MD',212500015,4105551234);
insert into employees values
(102,'Jen','Frey','123 UMBC Way','Baltimore','MD',212500015,4105551234);
insert into employees values
(103,'Joe','Smith','123 Sesame Street','Happville','MD',211100006,4105557177);
insert into employees values
(104,'Mike','Simmons','123 Ford Street','Baltimore','MD',212507176,4105551234);
insert into employees values
(105,'Homer','Simpson','742 Evergreen Terrace','Springfield','NT',49070000,1235555555);
insert into devices values
(1001,'HP 4000 Laser','123abc');
insert into devices values
(1002,'3.5" Floppy Drive','77745356562');
insert into devices values
(1003,'Sony 15" Monitor','sony78708122');
insert into devices values
(1004,'5.1GB Hard Drive','fasi86jasd689');
insert into devices values
(1005,'64MB ECC Dimm','1257564712768');
insert into devices values
(1006,'Microsoft Serial Mouse','ms5hgyhbhgh');
insert into devices values
(1007,'Microsoft Natural Keyboard','msjhs7s78789hsa');
insert into systems values
(5001,'HP Net Server 5000','111345',6233);
insert into systems values
(4001,'HP Vectra 500','323122',5233);
insert into systems values
(4002,'HP Vectra 500','435645',5233);
insert into systems values
(4003,'IBM Intellistation','723587923876',6400);
insert into systems values
(4004,'Dell Custom Config','sdhsdga73792',5166);
insert into software values
('Wordperfect',8.05,50);
insert into software values
('Windows',98.01,35);
insert into software values
('Windows',95.03,75);
insert into software values
('Windows NT',4.04,15);
insert into software values
('MS Office',97.06,113);
insert into indlicenses values
('Wordperfect',8.05,'wp511');
insert into indlicenses values
('Wordperfect',8.05,'wp512');
insert into indlicenses values
('Wordperfect',8.05,'wp513');
insert into indlicenses values
('Wordperfect',8.05,'wp514');
insert into indlicenses values
('Wordperfect',8.05,'wp515');
insert into supplies values
('pencil','Bic',500);
insert into supplies values
('red pen','Bic',100);
insert into supplies values
('blue pen','Bic',200);
insert into supplies values
('black pen','Bic',500);
insert into supplies values
('pencil','3M',300);
insert into supplies values
('500ct laser paper','3M',100);
insert into supplies values
('Small Stapler','3M',50);
insert into furniture values
('oak executive desk','Oakwood Inc.',3001,'presidential suite');
insert into furniture values
('oak regular desk','Oakwood Inc.',3002,'room 101');
insert into furniture values
('oak regular desk','Oakwood Inc.',3003,'room 306');
insert into furniture values
('steel table','Steelman Inc.',3004,'copy room');
insert into assignedto values
(101,4001);
insert into assignedto values
(101,4002);
insert into assignedto values
(102,4003);
insert into assignedto values
(103,5001);
insert into assignedto values
(105,4004);
insert into locatedon values
('Wordperfect',8.05,'wp511',5001);
insert into locatedon values
('Wordperfect',8.05,'wp512',4001);
insert into locatedon values
('Wordperfect',8.05,'wp513',4002);
insert into locatedon values
('Wordperfect',8.05,'wp514',4003);
insert into locatedon values
('Wordperfect',8.05,'wp515',4004);
insert into partof values
(5001,1002);
insert into partof values
(4001,1003);
insert into partof values
(4001,1004);
insert into partof values
(5001,1005);
insert into partof values
(4002,1006);
insert into partof values
(4004,1007);
insert into hardware values
(5001,'Graphics Server','System');
insert into hardware values
(4001,'Fred''s Server','System');
insert into hardware values
(4002,'Fred''s Desktop','System');
insert into hardware values
(1001,'Main Printer','Device');
insert into assets values
('bic pencil','supply');
insert into assets values
('wordperfect','software');
insert into assets values
('executive desk','furniture');
insert into assets values
('Graphics Server','hardware');
insert into assets values
('3.5" floppy drive','harwdare');
SQL Report Queries:
1. What software is loaded on the system(s) assigned to Fred Frey?
select title, version, assetno
from locatedon
where locatedon.assetno in(
select assetno
from assignedto
where employeeid in(
select employeeid
from employees
where fname = 'Fred' and
lname = 'Frey'));
Result:
TITLE
VERSION ASSETNO
------------------------------ ---------- ----------
Wordperfect
8 4001
Wordperfect
8 4002
2. How many machines are there that are slower than a Pentium II 266?
select count(assetno) as slower_machines
from systems
where speed < 6266;
Result:
SLOWER_MACHINES
---------------
4
3. What is the average number of pencils each employee has assuming that
25% of the pencils are located in storage closets?
select (sum(quantity))/(count(employeeid))*.75 as Avg_Pencils
from supplies, employees
where description = 'pencil';
Result:
AVG_PENCILS
-----------
120
4. What are the asset numbers and descriptions of all the components of
the system with asset number 4001?
select assetno, devicename
from devices
where assetno in(
select device
from partof
where systemno = 4001);
Result:
---------- ------------------------------
1003 Sony 15" Monitor
1004 5.1GB Hard Drive
Ad-hoc Queries:
1.
List all software titles owned by the company ignoring versions.
select
unique title from software;
Result:
TITLE
------------------------------
MS
Office
Windows
Windows
NT
Wordperfect
2.
Display a phone directory of all employees in the company and sort by last
name.
select
lname, fname, phone
from employees
order by lname;
LNAME FNAME PHONE
------------------------------
------------------------------ ----------
Frey Fred 4105551234
Frey Jen 4105551234
Simmons Mike 4105551234
Simpson Homer 1235555555
Smith Joe 4105557177
3. Display a
list of all supplies in the company and the quantity owned by the company.
select
description, sum(quantity) as Total
from supplies
group by
description;
Result:
DESCRIPTION TOTAL
------------------------------
----------
500ct laser
paper 100
Small
Stapler 50
black
pen 500
blue pen 200
pencil 800
red pen 100
Data From Database:
Employees Table:
EMPLOYEEID FNAME
LNAME
---------- ------------------------------ ------------------------------
ADDRESS
CITY
ST ZIP
------------------------------ ------------------------------ --
----------
PHONE
----------
101 Fred Frey
123 UMBC Way
Baltimore
MD 212500015
4105551234
102 Jen Frey
123 UMBC Way
Baltimore
MD 212500015
4105551234
103 Joe Smith
123 Sesame Street
Happville
MD 211100006
4105557177
104 Mike Simmons
123 Ford Street
Baltimore
MD 212507176
4105551234
105 Homer Simpson
742 Evergreen Terrace
Springfield
NT 49070000
1235555555
Devices Table:
ASSETNO DEVICENAME SERIALNO
---------- ------------------------------ --------------------
1001 HP 4000 Laser 123abc
1002 3.5" Floppy
Drive 77745356562
1003 Sony 15" Monitor sony78708122
1004 5.1GB Hard Drive fasi86jasd689
1005 64MB ECC Dimm 1257564712768
1006 Microsoft Serial
Mouse ms5hgyhbhgh
1007 Microsoft Natural
Keyboard msjhs7s78789hsa
Systems Table:
ASSETNO DESCRIPTION SERIALNO
---------- ---------------------------------------- --------------------
SPEED
----------
5001 HP Net Server 5000 111345
6233
4001 HP Vectra 500 323122
5233
4002 HP Vectra 500 435645
5233
4003 IBM Intellistation 723587923876
6400
4004 Dell Custom Config sdhsdga73792
5166
Software Table:
TITLE
VERSION NUMLICENSES
------------------------------ ---------- -----------
Wordperfect
8 50
Windows
98 35
Windows
95 75
Windows NT
4 15
MS Office
97 113
Individual Licenses Table:
TITLE
VERSION SERIALNO
------------------------------ ---------- --------------------
Wordperfect
8 wp511
Wordperfect
8 wp512
Wordperfect
8 wp513
Wordperfect
8 wp514
Wordperfect
8 wp515
Supplies Table:
DESCRIPTION
MANF QUANTITY
------------------------------ -------------------- ----------
pencil
Bic 500
red pen Bic 100
blue pen
Bic 200
black pen
Bic 500
pencil
3M 300
500ct laser paper
3M 100
Small Stapler
3M 50
Furniture Table:
DESCRIPTION
MANF ASSETNO
------------------------------ -------------------- ----------
LOCATION
--------------------
oak executive desk
Oakwood Inc. 3001
presidential suite
oak regular desk
Oakwood Inc. 3002
room 101
oak regular desk
Oakwood Inc. 3003
room 306
steel table
Steelman Inc. 3004
copy room
Assigned To Table:
EMPLOYEEID ASSETNO
---------- ----------
101 4001
101 4002
102 4003
103 5001
105 4004
Located On Table:
TITLE
VERSION SERIALNO
ASSETNO
------------------------------ ---------- -------------------- ----------
Wordperfect
8 wp511 5001
Wordperfect 8 wp512 4001
Wordperfect
8 wp513 4002
Wordperfect
8 wp514 4003
Wordperfect
8 wp515 4004
Hardware Table:
ASSETNO DESCRIPTION TYPE
---------- ------------------------------ ----------
5001 Graphics Server System
4001 Fred's Server System
4002 Fred's Desktop System
1001 Main Printer Device
Assets Table:
DESCRIPTION
TYPE
------------------------------ ----------
bic pencil
supply
wordperfect
software
executive desk
furniture
Graphics Server
hardware
3.5" floppy drive
harwdare
Part Of Table:
SYSTEMNO DEVICE
---------- ----------
5001 1002
4001 1003
4001 1004
5001 1005
4002 1006
4004 1007
Who Has What View:
FNAME LNAME
------------------------------
------------------------------
DESCRIPTION
----------------------------------------
Fred Frey
HP Vectra 500
Fred Frey
HP Vectra 500
Jen Frey
IBM
Intellistation
Joe Smith
HP Net Server
5000
Homer Simpson
Dell Custom
Config