Branch (Bno, Area, City)

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 () gt11)

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 () gt11),
• PRIMARY KEY (sno),
• FOREIGN KEY (bno) REFERENCES branch (bno) ON
  DELETE SET NULL ON UPDATE CASCADE)