Project Question 557 Ch 5 Physical Database Design Perform

Project Question 5-57 (Ch 5 - Physical Database Design & Performance

We\'re being asked to create a data dictionary similar to the metadata table shown in Table 1-1 in Chapter 1 to document our choices. For each table in the relational schema developed earlier, provide the following information for each field/ data element: field name, definition/description, data type, format, allowable values, whether the field is required or optional, whether the field is indexed and the type of index, whether the field is a primary key, whether the field is a foreign key, and the table referenced by the foreign key field. I\'m not sure how to do this, any assistance on this? The solution provided is completely wrong, doesn\'t even reference the FAME Case Study that we\'ve been working on all semester.

1. Customer Receives Customer ID# Customer Name P Customer Address Places AA 2. Orders Artist Location Date Time Fee Generates Completes 3. Contract Issued Receives 4. Manager Manager Name Artist ID# Issues Issues Accepts 7. Billing Creates Invoice Tracks 6. Calendar 5. Artist Artist ID# Artist Name Artist Gender Artist Address Country Phone# Email Issues Receives

Solution

I have listed few examples for different kind of tables with syntax creation. See the examples below

Person_T table is below:

CREATE TABLE Persons

(

PersonID int,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255)

);

Physician_T table is below:

create table Physician

(

phregno varchar2(5),

phname varchar2(15) NOT NULL,

phadd varchar2(20) NOT NULL,

phtelno number(9),

PRIMARY KEY(phreg no)

);

Patient_T table is below:

CREATE TABLE Patient

(

RecordNumber Text(10) PRIMARY KEY,

FirstName Text(30),

LastName Text(40),

SSN Text(9)

);

Visits_T table is below:

create table Visits

(

phregno varchar2(5),

ptname varchar2(15),

date_of_visit date,

feescharged number(5) NOT NULL,

PRIMARY KEY(phregno,ptname,date_of_visit),

FOREIGN KEY (phregno) references Physician,

FOREIGN KEY (ptname) references Patient

);

diagnosis_T table is below:

CREATE TABLE diagnosis

(

ID INTEGER PRIMARY KEY,

PatientRecordNumber Text(10) NOT NULL,

Code INTEGER,

DiagnosisDate Date,

CONSTRAINT fk1 FOREIGN KEY (PatientRecordNumber) REFERENCES patient (RecordNumber)

);

CUSTOMERS_T table is below:

CREATE TABLE CUSTOMERS

(

ID   INT              NOT NULL,

NAME VARCHAR (20)     NOT NULL,

AGE INT              NOT NULL,

ADDRESS CHAR (25) ,

SALARY   DECIMAL (18, 2),    

PRIMARY KEY (ID)

);