Quote

Develop a script to create the four tables in the Campus schema shown below. Before any tables are created, your script should clean up the database (drop all objects (tables) created by your script). Create a primary key for each table and create the proper foreign keys to maintain all relationships between the tables. All constraints must be defined in the CREATE TABLE state-ment.

Campus Schema

Campus
Name Provost Phone Zip Opened
Alexandria M. Annette Haggray (703) 323-3000 22311 1965
Annandale Pam Hilbert (703) 323-3158 22003 1966
Loudoun Julie Leidig (703) 450-2505 20164 1974
Manassa Molly Lynch (703) 257-6685 20109 1967

Dorm
Name Campus Dining Num_Floors Num_Rooms
Branner Hall Loudoun Y 5 200
Crothers Hall Annandale N 3 125
Robie Hall Manassas N 4 140
Stern Hall Manassas Y 4 180
Toyon Hall Alexandria Y 3 90
Wilbur Hall Annandale Y 5 180

Room_Assign
SID Dorm_Name Room_no Deposit Assign_Date
121 Stern 415 1200 7/28/16
238 Robie 222 795 6/13/16
249 Wilbur 538 825 7/12/16
187 Stern 249 1200 6/30/16`
375 Wilbur 538 825 8/5/16
238 Robie 316 900 8/1/16

Student
SID First Last Rank GPA Gender
121 Mary Jones SO 3.15 F
238 Jim Stewart SR 2.85 M
249 Bob Smith SR 3.89 M
187 Sue Crowe FR 3.05 F
375 Jill Mayer JR 2.44 F
Create constraints to enforce the following business rules. If a business rule cannot be imple-mented with constraints, explain why in the D2L comments for this assignment.

1. Campus provost is required
2. Gender may only contain F or M
3. Minimum GPA is 0, maximum GPA is 4
4. All deposits for the same room (same dorm, room, year) must be equal
5. Dinning may only contain Y or N
6. Room deposit may not be negative
7. Every dorm must have a campus
8. Rank may only contain FR, SO, JR, or SR
9. No two campuses may have the same phone number

Write DML statements to insert all of the data shown in the schema diagram above. Your insert statements must not fail if additional columns are added to the tables. Write DML statements to test at least two constraints.

Write DML statements to record the following events.

1. Robie Hall was renovated over the summer and now provides dinning services.
2. Amy Marshall, a new freshman, was assigned to Crothers room 301 on August 12, 2016. Amy paid a deposit of $500.
3. Sue Crowe was not happy with her roommate. She was reassigned to Stern 218.
4. Mary Jones left the University.
5. All students received a 10% bonus for their deposit. The additional 10% was added to the amount of their deposit.

Add a comment to your script and explain the implications of the primary key in the Room Assign table. Specifically, what does this key allow and not allow with respect to assigning rooms?

Quote

CREATE TABLE Campus
( Name varchar(20), Provost varchar(55) NOT NULL, Phone varchar(20), Zip int, Opened int)


INSERT INTO Campus
( Name , Provost , Phone , Zip , Opened )
VALUES
('Alexandria', 'M. Annette Haggray','(703) 323-3000',22311,1965),
('Annandale', 'Pam Hilbert','(703) 323-3158',22003,1966),
('Loudoun', 'Julie Leidig','(703) 450-2505',20164,1974),
('Manassa', 'Molly Lynch','(703) 257-6685',20109,1967)


CREATE TABLE Dorm
( Name varchar(20), Campus varchar(20) NOT NULL, Dining varchar(1), Num_Floors varchar(20), Num_Rooms varchar(20),
CONSTRAINT chk_Dorm CHECK (Dining = 'Y' or Dining = 'N'))


INSERT INTO Dorm
( Name , Campus , Dining , Num_Floors , Num_Rooms )
VALUES
('Branner Hall', 'Loudon','Y',5,200),
('Crothers Hall', 'Annandale','N',3,125),
('Robie Hall', 'Manassas','N',4,140),
('Stern Hall', 'Manassas','Y',4,180),
('Toyon Hall', 'Alexandria','Y',3,90),
('Wilbur Hall', 'Annandale','Y',5,180)

CREATE TABLE Room_Assign
( SID varchar(20), Dorm_Name varchar(20), Room_no varchar(5), Deposit varchar(20), Assign_Date varchar(20)
CONSTRAINT chk_Room_Assign CHECK (Deposit >= 0)


INSERT INTO Room_Assign
( SID , Dorm_Name , Room_no , Deposit , Assign_Date )
VALUES
(121,'Stern',415,1200,'7/28/16'),
(238, 'Robie',222,795,'6/13/16'),
(249, 'Wilbur',538,825,'7/12/16'),
(187,'Stern',249,1200,'6/30/16'),
(375,'Wilbur',538,825,'8/5/16'),
(238,'Robie',316,900,'8/1/16')

CREATE TABLE Student
( SID varchar(20), First varchar(20), Last varchar(20), Rank varchar(20), GPA int,'Gender' varchar(1),
CONSTRAINT chk_Student CHECK (Studen='FR' or Student = 'SO' or Student = 'JR' or Student = 'SR' and Gender='M' or Gender='F' and GPA <= 4 or GPA >=0))


INSERT INTO Student
( SID , First , Last , Rank , GPA , 'Gender')
VALUES
(121,'Mary','Jones','SO',3.15,'F'),
(238,'Jim','Stewart','SR',2.85,'M'),
(249,'Bob','Smith','SR',3.89,'M'),
(187,'Sue', 'Crowe','FR',3.05,'F'),
(375,'Jill','Mayer','JR',2.44,'F')

Quote (Zom8 @ Sep 18 2016 11:13pm)

Any debugging help would be appreciated

Quote

1. Campus provost is required
2. Gender may only contain F or M
3. Minimum GPA is 0, maximum GPA is 4
4. All deposits for the same room (same dorm, room, year) must be equal
5. Dinning may only contain Y or N
6. Room deposit may not be negative
7. Every dorm must have a campus
8. Rank may only contain FR, SO, JR, or SR
9. No two campuses may have the same phone number