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:
<![CDATA[
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.
<![CDATA[
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.
<![CDATA[
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:
<![CDATA[
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:
<![CDATA[
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.
File: project_ex.html Date: Sat Nov 20 21:19:04 EST 1999
All materials Copyright, 1997, 1998, 1999 Richard Holowczak