Database Design: Notes

__________________________________________________________________________________

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 )