1 / 14

Branch (Bno, Area, City) Staff (Sno, Name, Position, Sex, Salary, Bno)

Branch (Bno, Area, City) Staff (Sno, Name, Position, Sex, Salary, Bno) Property_for Rent (Pno, Area, City, Type, Rooms, Rent, Ono, Sno, Bno) Renter (Rno, Name, Max_Rent) Owner ( Ono, Name,) Viewing (Rno, Pno, Date). Student (Stuid, Stuname, Major, Credits)

teva
Download Presentation

Branch (Bno, Area, City) Staff (Sno, Name, Position, Sex, Salary, Bno)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Branch (Bno, Area, City) • Staff (Sno, Name, Position, Sex, Salary, Bno) • Property_for Rent (Pno, Area, City, Type, Rooms, Rent, Ono, Sno, Bno) • Renter (Rno, Name, Max_Rent) • Owner ( Ono, Name,) • Viewing (Rno, Pno, Date)

  2. Student (Stuid, Stuname, Major, Credits) • Class (Course#, Facid, Sched, Room) • Faculty (Facid, Facname, Dept, Rank) • Enrollment (Course#, Stuid, Grade)

  3. Define City for Branch according to the following constraints: • Has to have a two character string value • Has to be equal to SF, NY, LA, or DC • Set default value to SF

  4. City AS CHAR(2) DEFAULT ‘SF’ CHECK (VALUE IN (‘SF’, ‘NY’, ‘LA’, ‘DC’))

  5. Define Sno for Staff according to the following constraints: • Has to have a three digits integer • Has to be between 111 and 999 • Has to be unique

  6. Sno AS SMALLINT NOT NULL UNIQUE CHECK (VALUE BETWEEN 111 AND 999)

  7. Define a domain citylocation for City according to the following constraints: • Has a two character string value • Has to be equal to SF, NY, LA, or DC • Set default value to SF • Define the City in the Branch or Property_for_Rent

  8. CREATE DOMAIN citylocation AS CHAR(2) DEFACULT ‘SF’ CHECK (VALUE IN (‘SF’, ‘NY’, ‘LA’, ‘DC’)); • City citylocation NOT NULL

  9. Define a domain noforsno according to the following constraints: • Has to have a three digits integer • Has to be equal to one of the sno in the Staff • Define the sno in the Property_for_rent using the domain noforsno

  10. Sno AS SMALLINT NOT NULL UNIQUE CHECK (VALUE BETWEEN 111 AND 999) • CREATE DOMAIN noforsno AS CHAR(3) CHECK (VALUE IN (SELECT sno FROM staff)); • psno sno NOT NULL

  11. Define an assertion that will limit 10 staff members in any branch

  12. CREATE ASSERTION snolimit CHECK (NOT EXIST (SELECT bno FROM staff GROUP BY bno HAVING COUNT (*) >11);

  13. Create staff table using following constraints • Sno is three digits integer, between 111 and 999, a primary key • Name and position are 30 alphanumeric fields • Sex has a default value (m) limits to m or f • Salary is between 50,000 and & 100,000 • Bno is equal to one value of bno in branch with 10 or less staff member, set null for delete, set cascade for update

  14. CREAT TABLE staff • (Sno AS SMALLINT NOT NULL UNIQUE CHECK (VALUE BETWEEN 111 AND 999), • name CHAR(30) NOT NULL, • position CHAR(30) NOT NULL, • sex CHAR(1) NOT NULL DEFAULT ‘m’ CHECK (VALUE IN (‘m’, ‘f’)), • Salary NUMBER (8,2) NOT NULL CHECK (VALUE BETWEEN 50000 AND 100000), • Bno SMALLINT NOT NULL CONSTRAINT snolimit CHECK (NOT EXIST (SELECT bno FROM staff GROUP BY bno HAVING COUNT (*) >11), • PRIMARY KEY (sno), • FOREIGN KEY (bno) REFERENCES branch (bno) ON DELETE SET NULL ON UPDATE CASCADE);

More Related