Database Management Systems II - Prof. Holowczak

Baruch College School of Business
City University of New York

Database Management Systems II
Group Project Examples


Class Projects Examples

The following is an example of SQL CREATE TABLE, GRANT and CREATE SYNONYM statements.

Groups are encouraged to maintain all of their statements in a collection of files to allow for rapid re-construction of the schema and applications should any changes be required.

Assume there are three group members: Alice, Bill and Charlie. Alice will act as the schmea owner for the project team.

SQL CREATE TABLE file

Alice creates a file that includes all of the SQL CREATE TABLE statements: DROP TABLE EMPLOYEE ; DROP TABLE DEPARTMENT ; DROP TABLE DEPT_LOCATIONS ; DROP TABLE DEPENDENT ; CREATE TABLE EMPLOYEE (FNAME VARCHAR(8), MINIT VARCHAR(2), LNAME VARCHAR(8), SSN NUMBER(12) NOT NULL, BDATE DATE, ADDRESS VARCHAR(27), SEX VARCHAR(1) CHECK (SEX IN ('M', 'F')), SALARY NUMBER(7) NOT NULL, SUPERSSN NUMBER(9), DNO NUMBER(2) NOT NULL) ; CREATE TABLE DEPARTMENT (DNAME VARCHAR(15), DNUMBER NUMBER(2) NOT NULL, MGRSSN NUMBER(12), MGRSTARTDATE DATE) ; CREATE TABLE DEPT_LOCATIONS (DNUMBER NUMBER, DLOCATION VARCHAR(15)) ; CREATE TABLE DEPENDENT (ESSN NUMBER NOT NULL, DEPENDENT_NAME VARCHAR(10) NOT NULL, SEX VARCHAR(1) CHECK (SEX IN ('M', 'F')), BDATE DATE, RELATIONSHIP VARCHAR(10)) ;

SQL ALTER TABLE ADD CONSTRAINT file

Alice then creates a separate file with ALTER TABLE statements that are used to add constraints to the tables.

ALTER TABLE employee ADD CONSTRAINT pk_employee PRIMARY KEY (ssn) ; ALTER TABLE department ADD CONSTRAINT pk_department PRIMARY KEY (dnumber); ALTER TABLE employee ADD CONSTRAINT fk_department FOREIGN KEY (dno) REFERENCES department (dnumber); ALTER TABLE dependent ADD CONSTRAINT pk_dependent PRIMARY KEY (essn, dependent_name); ALTER TABLE dependent ADD CONSTRAINT fk_employee FOREIGN KEY (essn) REFERENCES employee (ssn);

SQL INSERT file

Alice now creates some sample data by writing some SQL insert statements. Notice that Copy/Paste can be effectively used to rapidly generate sample data.

Also, due to the Foreign Key constraints, department data must be entered first.

INSERT INTO DEPARTMENT VALUES ('RESEARCH',5,333445555,'22-MAY-1978') ; INSERT INTO DEPARTMENT VALUES ('ADMINISTRATION',4,987654321,'01-JAN-1985') ; INSERT INTO DEPARTMENT VALUES ('HEADQUARTERS',1,888665555,'19-JUN-1971') ; INSERT INTO EMPLOYEE VALUES ('JOHN','B','SMITH',123456789,'09-JAN-1955','731 FONDREN, HOUSTON, TX', 'M',30000,333445555,5) ; INSERT INTO EMPLOYEE VALUES ('FRANKLIN','T','WONG',333445555,'08-DEC-1945','638 VOSS,HOUSTON TX', 'M',40000,888665555,5) ; INSERT INTO EMPLOYEE VALUES ('ALICIA','J','ZELAYA',999887777,'19-JUL-1958','3321 CASTLE, SPRING, TX', 'F',25000,987654321,4) ; INSERT INTO EMPLOYEE VALUES ('JENNIFER','S','WALLACE',987654321,'20-JUN-1931','291 BERRY, BELLAIRE, TX', 'F',43000,888665555,4) ; INSERT INTO EMPLOYEE VALUES ('RAMESH','K','NARAYAN',666884444,'15-SEP-1952','975 FIRE OAK, HUMBLE, TX', 'M',38000,333445555,5) ; INSERT INTO EMPLOYEE VALUES ('JOYCE','A','ENGLISH',453453453,'31-JUL-1962','5631 RICE, HOUSTON, TX', 'F',25000,333445555,5); INSERT INTO EMPLOYEE VALUES ('AHMAD','V','JABBAR',987987987,'29-MAR-1959','980 DALLAS, HOUSTON, TX', 'M',25000,987654321,4) ; INSERT INTO EMPLOYEE VALUES ('JAMES','E','BORG',888665555,'10-NOV-1927', '450 STONE, HOUSTON, TX', 'M',25000,NULL,1) ; INSERT INTO DEPT_LOCATIONS VALUES (1,'HOUSTON') ; INSERT INTO DEPT_LOCATIONS VALUES (4,'STAFFORD') ; INSERT INTO DEPT_LOCATIONS VALUES (5,'BELLAIRE') ; INSERT INTO DEPT_LOCATIONS VALUES (5,'SUGARLAND') ; INSERT INTO DEPT_LOCATIONS VALUES (5,'HOUSTON') ; INSERT INTO DEPENDENT VALUES (333445555,'ALICE','F','05-APR-1976','DAUGHTER') ; INSERT INTO DEPENDENT VALUES (333445555,'THEODORE','M','25-OCT-1973','SON') ; INSERT INTO DEPENDENT VALUES (333445555,'JOY','F','03-MAY-1948','SPOUSE'); INSERT INTO DEPENDENT VALUES (123456789,'MICHAEL','M','01-JAN-1978','SON'); INSERT INTO DEPENDENT VALUES (123456789,'ALICE','F','31-DEC-1978','DAUGHTER'); INSERT INTO DEPENDENT VALUES (123456789,'ELIZABETH','F','05-MAY-1957','SPOUSE') ; INSERT INTO DEPENDENT VALUES (987654321,'ABNER','M','26-FEB-1932','SPOUSE');

SQL GRANT file

At this point, Alice has created some tables, added constraints and added some sample data in her schema (called ALICE). She can also add additional indexes, Oracle sequences and other objects such as stored procedures and triggers.

Now Alice must share these database objects with other group members. The first thing she does is create a set of SQL GRANT statements to allow Bill and Charlie to have access to the tables in her schema:

GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO bill ; GRANT SELECT, INSERT, UPDATE, DELETE ON department TO bill ; GRANT SELECT, INSERT, UPDATE, DELETE ON department_locations TO bill ; GRANT SELECT, INSERT, UPDATE, DELETE ON dependents TO bill ; GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO charlie ; GRANT SELECT, INSERT, UPDATE, DELETE ON department TO charlie ; GRANT SELECT, INSERT, UPDATE, DELETE ON department_locations TO charlie ; GRANT SELECT, INSERT, UPDATE, DELETE ON dependents TO charlie ;

Now Bill and Charlie can access the data in Alice's schema using statements such as: SELECT * FROM alice.employee ;

However, if the schema owner changes, all of the references to alice.table_name would need to be changed. In order to make the references to schema objects uniform, SYNONYMS can be created in each of the group members schemas.

CREATE SYNONYM file

Both Bill and Charlie will need to execute the following CREATE SYNONYM statements in their own schemas: CREATE SYNONYM employee FOR alice.employee; CREATE SYNONYM department FOR alice.department; CREATE SYNONYM department_locations FOR alice.department_locations; CREATE SYNONYM dependents FOR alice.dependents;

Once these CREATE SYNONYM statements have been executed, Bill and Charlie can then reference the tables (and other schema objects) by name rather than by explicitly referencing Alice's tables.


[Home]
File: project_ex.html Date: Sat Nov 20 21:19:04 EST 1999
All materials Copyright, 1997, 1998, 1999 Richard Holowczak