Nov 25, 2014 - Given the following relations, create a new user in your Oracle database; grant sufficient privileges the
Islamic University of Gaza Faculty of Engineering Computer Engineering Dept. Database Lab (ECOM 4113)
Lab 6 Exercises Solutions
Data Definition Language
Eng. Mohammed Alokshiya
November 25, 2014
Exercises Solutions: Given the following relations, create a new user in your Oracle database; grant sufficient privileges then create all the tables with appropriate constraints.
2
Solution: 1. Create a new user with sufficient privileges SYSTEM Connection CREATE USER REGISTRATION IDENTIFIED BY REGISTRATION DEFAULT TABLESPACE USERS QUOTA 10M ON USERS; GRANT CONNECT, RESOURCE TO REGISTRATION;
2. Connect to the new user and create required tables REGISTRATION Connection CREATE TABLE OWNER ( OWNER_ID NUMBER(6) PRIMARY KEY ); CREATE TABLE PERSON ( SSN NUMBER(6) PRIMARY KEY, DRIVER_LICIENCE_NO NUMBER(10) UNIQUE, NAME VARCHAR2(40) NOT NULL, ADDRESS VARCHAR2(100), OWNER_ID NUMBER(6) REFERENCES OWNER(OWNER_ID) ); CREATE TABLE BANK ( BNAME VARCHAR2(40) PRIMARY KEY, BADDRESS VARCHAR2(100), OWNER_ID NUMBER(6) REFERENCES OWNER(OWNER_ID) ); CREATE TABLE COMPANY ( CNAME VARCHAR2(40) PRIMARY KEY, CADDRESS VARCHAR2(100), OWNER_ID NUMBER(6) REFERENCES OWNER(OWNER_ID) ); CREATE TABLE REGISTERED_VEHICLE ( VEHICLE_ID NUMBER(10) PRIMARY KEY, LICIENCE_PLATE_NUMBER NUMBER(10) ); CREATE TABLE CAR ( VEHICLE_ID NUMBER(10) PRIMARY KEY REFERENCES REGISTERED_VEHICLE(VEHICLE_ID), CSTYLE VARCHAR2(10), CMAKE VARCHAR2(10), CMODEL VARCHAR2(10), CYEAR DATE );
3
CREATE TABLE TRUCK ( VEHICLE_ID NUMBER(10) PRIMARY KEY REFERENCES REGISTERED_VEHICLE(VEHICLE_ID), TMAKE VARCHAR2(10), CMODEL VARCHAR2(10), TONNAGE NUMBER(3), TYEAR DATE ); CREATE TABLE OWNS ( OWNER_ID NUMBER(6) REFERENCES OWNER(OWNER_ID), VEHICLE_ID NUMBER(10) REFERENCES REGISTERED_VEHICLE(VEHICLE_ID), PURCHASE_DATE DATE, LIEN_OR_REGULAR NUMBER(1) CHECK (LIEN_OR_REGULAR BETWEEN 0 AND 1), PRIMARY KEY(OWNER_ID, VEHICLE_ID) );
4
