Changes

From CDOT Wiki
Jump to: navigation, search

J.Y.S

3,438 bytes added, 16:01, 27 November 2010
no edit summary
2. AGENCY
<pre>
CREATE TABLE AGENCY
(
AgencyID NUMBER(4) CONSTRAINT agency_agencyid_pk PRIMARY KEY,
AgencyName VARCHAR2(40) NOT NULL CONSTRAINT agency_agencyname_uk UNIQUE,
AgencyPhone VARCHAR2(15) NOT NULL CONSTRAINT agency_agencyphone_uk UNIQUE
);
</pre>
3. PROPERTY
<pre>
CREATE TABLE PROPERTY
(
PropertyID NUMBER(6) CONSTRAINT property_propertyid_pk PRIMARY KEY,
Street VARCHAR2(40) NOT NULL,
City VARCHAR2(30) NOT NULL,
Province CHAR(2) NOT NULL,
PostalCode CHAR(6) NOT NULL,
Bedrooms NUMBER(1) NOT NULL
CONSTRAINT property_bedrooms_ck CHECK(Bedrooms BETWEEN 1 AND 9),
Bathrooms NUMBER(1) NOT NULL
CONSTRAINT property_bathrooms_ck CHECK(Bathrooms BETWEEN 1 AND 9),
ListingPrice NUMBER(6) NOT NULL
CONSTRAINT property_listingprice_ck CHECK(ListingPrice BETWEEN 1 AND 4999999),
AreaID NUMBER(4) NOT NULL
CONSTRAINT property_areaid_fk FOREIGN KEY(AreaID) REFERENCES AREA(AreaID)
);
</pre>
4. SINGLE
<pre>
CREATE TABLE SINGLE
(
PropertyID NUMBER(6) CONSTRAINT single_propertyID_pk PRIMARY KEY
CONSTRAINT single_propertyID_fk FOREIGN KEY(PropertyID)
REFERENCES PROPERTY(PropertyID),
LotSize NUMBER(8,2) NOT NULL CONSTRAINT single_lotsize_ck CHECK(LotSize > 0)
);
 
</pre>
5. CONDO
<pre>
CREATE TABLE CONDO
(
PropertyID NUMBER(6) CONSTRAINT condo_propertyID_pk PRIMARY KEY
CONSTRAINT condo_propertyID_fk FOREIGN KEY(PropertyID)
REFERENCES PROPERTY(PropertyID),
Fee NUMBER(8,2) NOT NULL CONSTRAINT condo_fee_ck CHECK(Fee > 0)
);
 
</pre>
6. OUTLET
<pre>
CREATE TABLE OUTLET
(
OutletID NUMBER(4),
OutletName VARCHAR2(40) NOT NULL,
OutletPhone VARCHAR2(15) NOT NULL,
OutletType CHAR(1) NOT NULL,
CONSTRAINT outlet_outletid_pk PRIMARY KEY(OutletID),
CONSTRAINT outlet_outlettype_ck CHECK(OutletType IN('N','M','W','F')),
CONSTRAINT outlet_outletname_type_uk UNIQUE(OutletName, OutletType)
);
 
</pre>
7. ADVERTISEMENT
<pre>
CREATE TABLE OUTLET
(
OutletID NUMBER(4),
OutletName VARCHAR2(40) NOT NULL,
OutletPhone VARCHAR2(15) NOT NULL,
OutletType CHAR(1) NOT NULL,
CONSTRAINT outlet_outletid_pk PRIMARY KEY(OutletID),
CONSTRAINT outlet_outlettype_ck CHECK(OutletType IN('N','M','W','F')),
CONSTRAINT outlet_outletname_type_uk UNIQUE(OutletName, OutletType)
);
</pre>
8. CLIENT
<pre>
CREATE TABLE CLIENT
(
ClientID NUMBER(6),
Fname VARCHAR2(30) NOT NULL,
Lname VARCHAR2(30) NOT NULL,
Phone VARCHAR2(15) NOT NULL,
Email VARCHAR2(40),
RefID NUMBER(6),
CONSTRAINT client_clientid_pk PRIMARY KEY(ClientID),
CONSTRAINT client_phone_uk UNIQUE(Phone),
CONSTRAINT client_email_uk UNIQUE(Email),
CONSTRAINT client_refid_fk FOREIGN KEY(RefID) REFERENCES CLEINT(ClientID)
);
</pre>
9. DEED
<pre>
CREATE TABLE DEED
(
PropertyID NUMBER(6),
SellerID NUMBER(6),
OwnPct NUMBER(5,2) NOT NULL,
BuyerID NUMBER(6),
BuyPrice NUMBER(7),
CONSTRAINT deed_deedid_pk PRIMARY KEY(PropertyID, SellerID),
CONSTRAINT deed_propertyid_fk FOREIGN KEY(PropertyID) REFERENCES PROPERTY(PropertyID),
CONSTRAINT deed_sellerid_fk FOREIGN KEY(SellerID) REFERENCES CLIENT(ClientID),
CONSTRAINT deed_buyerid_fk FOREIGN KEY(BuyerID) REFERENCES CLIENT(ClientID),
CONSTRAINT deed_ownpct_ck CHECK(OwnPct > 0 AND OwnPct <= 100),
CONSTRAINT deed_buyprice_ck CHECK(BuyPrice BETWEEN 1 AND 4999999)
);
 
</pre>
=Assignment 1=

Navigation menu