write programs using Oracle virtual box

Wentworth Institute of Technology
Comp355 Database Management Systems
Spring 2014
Lab 5
Creating Tables
1. Create the tables for the Henry Books Database, according to the specifications
below. Please be sure to include referential integrity constraints by specifying
foreign keys.
Let’s write the command for creating the branch table, just to get you started.
create table branch
(branch_number varchar2(1) primary key,
branch_name varchar2(20),
branch_location varchar2(20),
number_employees number(2));
When creating a foreign key, please use the following syntax next to the name of the
field:
branch_number varchar2(1) references branch(branch_number)
One other thing to keep in mind is that you will need to use a column constraint for
the wrote and invent table, since these both have composite primary keys. In order
to do this, you must include a constraint clause after the last column definition (after
a comma):
constraint pk1 primary key (book_code,author_number)
Table Name: Branch
Column Type Length Decimal
Places
Nulls
Allowed?
Primary
Key?
Related
Table
Branch_Number Varchar2 1 No Yes
Branch_Name Varchar2 20
Branch_Location Varchar2 20
Number_Employees Number 4 0
Table Name: Publisher
Column
Type
Length
Decimal
Places
Nulls Allowed?
Primary Key?
Related Table
Publisher_Code
Varchar2
2
No
Yes
Publisher_Name
Varchar2
20
Publisher_City
Varchar2
20
Publisher_State
Varchar2
2
Table Name: Author
Column
Type
Length
Decimal
Places
Nulls Allowed?
Primary Key?
Related Table
Author_Number
Varchar2
2
No
Yes
Author_Name
Varchar2
20
Author_First
Varchar2
20
Table Name: Book
Column
Type
Length
Decimal
Places
Nulls Allowed?
Primary Key?
Related Table
Book_Code
VarChar2
4
No
Yes
Book_Title
Varchar2
30
Publisher_Code
Varchar2
2
Publisher
Book_Type
Varchar2
30
Book_Price
Number
4
2
Paperback
Varchar2
1
Table Name: Wrote
Column
Type
Length
Decimal
Places
Nulls Allowed?
Primary Key?
Related Table
Book_Code
Varchar2
4
No
Yes
Book
Author_Number
Varchar2
2
Yes
Author
Sequence_Number
Number
1
0
Table Name: Invent
Column
Type
Length
Decimal
Places
Nulls Allowed?
Primary Key?
Related Table
Book_Code
Varchar2
4
No
Yes
Book
Branch_Number
Varchar2
1
Yes
Branch
Units_On_Hand
Number
4
0
You should write a script and save it You can name this script books.sql
You should also create a script to drop the tables. You should make sure that the tables are dropped in the opposite order that they were created in. Name this script dropbooks.sql
Data Loading
Create a script called loadbooks.sql This script will load all of the data into the tables based. Use the data that I have provided below. The general syntax of the insert command is:
Insert into tablename}
Values ({field1},{field2},…,{fieldn});
So, for example, to insert one line into branch, we would write:
Insert into branch
Values (‘1’,’Main’,’Boston’,26);
Also, please make sure that you insert in the order of the table creation scripts. You only need to create one or two insert statements per table.
Table Name: Branch
Branch_Number
Branch_Name
Branch_Location
Number_Employees
1
Main
Boston
250
2
Metrowest
Framingham
100
3
Central
Worcester
55
4
West
Springfield
25
Table Name: Publisher
Publisher_Code
Publisher_Name
Publisher_City
Publisher_State
21
Prentice Hall
Upper Saddle Brook
NJ
22
Addison Wesley
Needham
MA
23
Brooks-Cole
Sudbury
MA
24
Mcgraw-Hill
New York
NY
Table Name: Author
Author_Number
Author_Name
Author_First
33
Williams
Samuel
34
Hanlon
Sarah
35
McBuckle
Arlo
36
Melville
Herman
Table Name: Book
Book_Code
Book_Title
Publisher_Code
Book_Type
Book_Price
Paperback
3921
Modern Databases
21
Technical
99.99
N
3922
Systems Analysis
22
Technical
88.77
Y
3923
Sea, Sand, Waves
24
Fiction
24.00
Y
3924
Extreme Databases
23
Fiction
34.23
N
Table Name: Wrote
Book_Code
Author_Number
Sequence_Number
3921
33
1
3921
34
2
3922
35
1
3923
36
1
3924
33
1
Table Name: Invent
Book_Code
Branch_Number
Units_On_Hand
3921
1
231
3921
2
100
3921
3
1
3922
1
1000
3922
4
109
3923
1
500
3923
2
300
3923
3
12
3924
1
876
3924
4
5
What to Hand In
Please turn in all completed scripts in a packet via Blackboard. You do not need to show the execution of each script.