database design

Create the database - write the SQL statements (DDL & Inserting Data) (25 pts) Use http://apex.oracle.com to create an Oracle database according to the ERD you have created in project part1. Checklist: · DDL statements to create/ drop the database tables. · Suitable data type for each field · Constraints (PK, FK, NOT NULL, CHECK and UNIQUE) were appropriate · Populate the database tables with a reasonable number of insert records. · Submit the script

SQL statements (Queries) (25 Pts) Write a minimum of 15 different queries following guidelines below: Checklist: • Write questions that the query should answer. • Write an SQL statement to answer the question. • Must demonstrate the use of the following: 1. Arithmetic Operators 2. Distinct 3. Concatenation 4. Comparison operators. 5. BETWEEN … AND 6. AND 7. OR 8. NOT 9. IN 10. LIKE 11. Multi table JOIN 12. SORT 13. Arithmetic functions (SUM, COUNT, AVG, MIN, MAX etc) · Provide screen captures as evidence of completing the tasks

This is an example of the assignment

DROP TABLE CRUISE_PASSENGER CASCADE CONSTRAINTS;

DROP TABLE PASSENGER CASCADE CONSTRAINTS;

DROP TABLE CRUISE_PORT CASCADE CONSTRAINTS;

DROP TABLE PORT CASCADE CONSTRAINTS;

DROP TABLE CRUISE CASCADE CONSTRAINTS;

DROP TABLE SAILOR CASCADE CONSTRAINTS;

DROP TABLE SHIP CASCADE CONSTRAINTS;

CREATE TABLE SHIP(

Ship_number Varchar2 (70) Primary key,

Ship_name Varchar (10),

Weight

);

CREATE TABLE SAILOR(

);

CREATE TABLE CRUISE(

);

CREATE TABLE PORT(

);

CREATE TABLE CRUISE_PORT(

);

CREATE TABLE PASSENGER(

);

CREATE TABLE CRUISE_PASSENGER(

);

THIS THIS IS THE EXAMPLE

DROP TABLE STUDENT_TEACHER_SUBJECT CASCADE CONSTRAINTS;

DROP TABLE SUBJECT CASCADE CONSTRAINTS;

DROP TABLE TEACHER_UNIVERSITY CASCADE CONSTRAINTS;

DROP TABLE UNIVERSITY CASCADE CONSTRAINTS;

DROP TABLE SCHOOL_TEACHER CASCADE CONSTRAINTS;

DROP TABLE TEACHER CASCADE CONSTRAINTS;

DROP TABLE STUDENT CASCADE CONSTRAINTS;

DROP TABLE ADMINISTRATOR CASCADE CONSTRAINTS;

DROP TABLE SCHOOL CASCADE CONSTRAINTS;

CREATE TABLE SCHOOL(

S_Name varchar2(50) PRIMARY KEY,

S_Address varchar2(50),

S_PNumber char(10),

S_YearBuilt number(4) check (S_YearBuilt between 1980 and 1985),

S_Size number(8)

);

CREATE TABLE ADMINISTRATOR(

Adm_EMPNumber varchar2(8)PRIMARY KEY,

ADM_Name varchar2(25),

Adm_PNumber char(10) NOT NULL,

Adm_OfficeNumber varchar2(10)check (Adm_OfficeNumber between 100 and 300) ,

S_Name varchar2(50) REFERENCES School(S_Name)

);

CREATE TABLE STUDENT(

Stu_Number char(8) PRIMARY KEY,

Stu_Name varchar2(25),

Stu_HomeAddress varchar(25),

Stu_PNumber char(10),

Stu_CurrentGrade varchar2(9) CHECK (Stu_CurrentGrade IN ('A', 'B', 'C', 'D', 'F')),

Stu_Age number(2)check (Stu_age between 17 and 35),

S_Name varchar2(50) ReferenceS school(S_Name) 

);

CREATE TABLE TEACHER(

T_EmpNumber char(10) PRIMARY KEY,

T_Name varchar2(50),

T_Age number(2) check (T_Age between 20 and 65),

T_SubjectSpeciality varchar2(50),

T_YearEnteredSystem char(4)

);

CREATE TABLE SCHOOL_TEACHER(

S_Name varchar2(50),

T_EmpNumber char(10) check(T_EmpNumber>=100),

T_YearEnteredSystem char(4)check(T_YearEnteredSystem>=1980),

T_highestPay number(7,2) check(T_highestPay>44000),

PRIMARY KEY(S_Name,T_EmpNumber),

FOREIGN KEY (S_Name) REFERENCES SCHOOL(S_Name),

FOREIGN KEY (T_EmpNumber) REFERENCES TEACHER(T_EmpNumber)

);

WRITE THE SQL STATEMENTS TO ADD (INSERT) DATA IN ALL THE TABLES ABOVE:

INSERT INTO SCHOOL (S_Name, S_Address, S_PNumber,S_YearBuilt,S_Size) VALUES ('John marshall', '75 clinton  ave','9733334578',1981, 300000);

INSERT INTO SCHOOL (S_Name, S_Address, S_PNumber,S_YearBuilt,S_Size) VALUES ('JOHN HOPKINS', '111 Elm Street','3012222222',1980, 100000);

INSERT INTO ADMINISTRATOR(Adm_EMPNumber, Adm_Name, Adm_PNumber, Adm_OfficeNumber, S_Name) VALUES ('0050863', 'Henry Smith', '2026798343', 243, 'JOHN HOPKINS');

INSERT INTO ADMINISTRATOR(Adm_EMPNumber, Adm_Name, Adm_PNumber, Adm_OfficeNumber, S_Name) VALUES ('00389322', 'Deez Nuuuts','2402262622', 299, 'John marshall');

INSERT INTO ADMINISTRATOR (Adm_EMPNumber, Adm_Name,  Adm_PNumber,Adm_OfficeNumber, S_name) VALUES ( '00239576', 'Daniel',

'3012736543', 300, 'John marshall');

INSERT INTO ADMINISTRATOR (Adm_EMPNumber, Adm_Name,  Adm_PNumber,Adm_OfficeNumber, S_name) VALUES ( '000134', 'Davis','3019085653','201', 'John marshall');

INSERT INTO STUDENT (Stu_Number, Stu_Name, Stu_HomeAddress, Stu_PNumber, Stu_CurrentGrade, Stu_Age, S_Name) VALUES ('00053492', 'Chase Tillman', '123 Hallway Avenue', '2024320980', 'A', '20', 'JOHN HOPKINS'); 

INSERT INTO STUDENT (Stu_Number, Stu_Name, Stu_HomeAddress, Stu_PNumber, Stu_CurrentGrade, Stu_Age, S_Name) VALUES ('00053792', 'Travis James', '123 Nutz Deez Lane', '2406635373', 'A', '19', 'John marshall');

INSERT INTO STUDENT (Stu_Number, Stu_Name, Stu_HomeAddress, Stu_PNumber, Stu_CurrentGrade, Stu_Age, S_Name) VALUES ('000099', 'Hurrich Engoue', '245 rue de lecroix', '2024320890', 'B', '24', 'John marshall');

INSERT INTO STUDENT  (Stu_Number, Stu_Name, Stu_HomeAddress, Stu_PNumber, Stu_CurrentGrade, Stu_Age, S_Name) VALUES ( '0186075', 'Lynnell White', '5151 54th Street', '2028829527', 'A', '21', 'JOHN HOPKINS'); 

INSERT INTO STUDENT (Stu_Number, Stu_Name, Stu_HomeAddress, Stu_PNumber, Stu_CurrentGrade, Stu_Age, S_Name) VALUES ('00057542', 'Herb Exume', '321 blow Avenue', '9733333333', 'B', '21', 'John marshall');