Database Design: Notes

Topics: SQL, Database, Data Definition Language Pages: 5 (619 words) Published: November 14, 2013
__________________________________________________________________________________

DATABASE DESIGN
I. Introduction
Database Application
What is an Employment Agency System?
An employment agency is an organization which matches employers to employees. In all developed countries there is a publicly funded employment agency and multiple private businesses which also act as employment agencies.

Business Rules
Business Rules
One or more Agencies fall into one or more Reference Agency Categories. One or more Agencies can have one or more address.
An Agency can have zero, one or many Contacts.
An employment database is required to maintain a listing of Employment agencies with details of website, head office, subsidiary offices and contact persons at each local office. Companies are categorized, for example "Administration", "IT", "Tutoring" depending on what kind of employment they specialize in. Some agencies may have only Administration jobs on offer, however other agencies may offer Administration jobs and IT jobs on offer- so they need multiple categories on the database.

II. Entity Relationship Diagram
Tables and Cardinalities

Entity Relationship Diagram

III. Data Definition Language
DDL SCRIPT
-- Generated by Oracle SQL Developer Data Modeler 4.0.0.820
-- at: 2013-11-06 04:53:59 CST
-- site: Oracle Database 11g
-- type: Oracle Database 11g

CREATE TABLE AgencyCateg
(
RefAgencyCateg_ag_categ_code NUMBER (30,3) ,
agencies_agency_id NUMBER (30,3)
) ;

CREATE TABLE RefAgencyCateg
(
ag_categ_code NUMBER (30,3) NOT NULL ,
ag_categ_desc VARCHAR2 (30) NOT NULL
) ;
ALTER TABLE RefAgencyCateg ADD CONSTRAINT RefAgencyCateg_PK PRIMARY KEY ( ag_categ_code ) ;

CREATE TABLE addresses
(
address_id NUMBER (30,2) NOT NULL ,
complete_address VARCHAR2 (60) NOT NULL ,
country VARCHAR2 (30) NOT NULL
) ;
ALTER TABLE addresses ADD CONSTRAINT addresses_PK PRIMARY KEY ( address_id ) ;

CREATE TABLE agencies
(
agency_id NUMBER (30,3) NOT NULL ,
agency_name VARCHAR2 (30) NOT NULL ,
agency_phone NUMBER (20,3) NOT NULL ,
agency_email VARCHAR2 (30) NOT NULL ,
other_agency_details VARCHAR2 (50) ,
addresses_address_id NUMBER (30,2)
) ;
ALTER TABLE agencies ADD CONSTRAINT agencies_PK PRIMARY KEY ( agency_id ) ;

CREATE TABLE contacts
(
contact_id NUMBER (30,2) NOT NULL ,
first_name VARCHAR2 (30) NOT NULL ,
middle_name VARCHAR2 (30) NOT NULL ,
last_name VARCHAR2 (30) NOT NULL ,
gender VARCHAR2 (10) NOT NULL ,
contact_phone NUMBER (30,2) NOT NULL ,
contact_email VARCHAR2 (30) NOT NULL ,
local_offices_office_id NUMBER (30,2)
) ;
ALTER TABLE contacts ADD CONSTRAINT contacts_PK PRIMARY KEY ( contact_id ) ;

CREATE TABLE local_offices
(
office_id NUMBER (30,2) NOT NULL ,
office_phone NUMBER (20,2) NOT NULL ,
office_email VARCHAR2 (30) NOT NULL ,
other_office_details VARCHAR2 (60) ,
addresses_address_id NUMBER (30,2) ,
agencies_agency_id NUMBER (30,3)
) ;
ALTER TABLE local_offices ADD CONSTRAINT local_offices_PK PRIMARY KEY ( office_id ) ;

ALTER TABLE AgencyCateg ADD CONSTRAINT AgencyCateg_RefAgencyCateg_FK FOREIGN KEY ( RefAgencyCateg_ag_categ_code ) REFERENCES RefAgencyCateg ( ag_categ_code ) ;

ALTER TABLE AgencyCateg ADD CONSTRAINT AgencyCateg_agencies_FK FOREIGN KEY ( agencies_agency_id ) REFERENCES agencies ( agency_id ) ;

ALTER TABLE agencies ADD CONSTRAINT agencies_addresses_FK FOREIGN KEY ( addresses_address_id ) REFERENCES addresses ( address_id ) ;

ALTER TABLE contacts ADD CONSTRAINT contacts_local_offices_FK FOREIGN KEY ( local_offices_office_id ) REFERENCES local_offices (...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Database Design and Implementation Essay
  • Database Design Process Essay
  • CIS Database Notes Essay
  • Why Good Database Design Is Important in Performing Data Operation? Essay
  • Essay on Design and Data
  • Essay about Dad (Data Analysis and Design)
  • Database Design Essay
  • The Difference Between Logical and Physical Network Design Essay

Become a StudyMode Member

Sign Up - It's Free