Description of this paper

write programs using Oracle virtual box

Description

solution


Question

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.

 

Paper#69464 | Written in 18-Jul-2015

Price : $27
SiteLock