P2. Create a data dictionary similar to the metadata table

P2. Create a data dictionary similar to the metadata table shown in Table 1-1 in Chapter 1 to document your choices. For each table in the relational schema you 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 that is referenced by the foreign key field.
TABLE: Person_T
Data Item Value
Name Type Length Min Max Description Index PK FK References
PersonID Varchar 5 Person ID x
PersonName Varchar Persons name
PersonStrAddress Varchar Person street address
PersonCity Varchar Person city
PersonState Char 2 2 Person state
PersonZip Varchar 9 Person ZIP Code
PersonHomePhone Varchar Person’s home phone
PersonWorkPhone Varchar Person’s work phone
PersonDOB Date Persons DOB
PersonEmail Varchar Persons email
Employee_T
Data Item Value
Name Type Length Min Max Description Index PK FK References
EmpID Varchar Employee ID x
DateHired Date Date employee hired
EmpType Varchar Employee type
Physician_T
Data Item Value
Name Type Length Min Max Description Index PK FK References
PhysicianID Varchar Physician ID x
DEANo Varchar DEA number
PagerNo Varchar Pager number
Specialty Varchar Physician specialty
PhysicianDX_T
Data Item Value
Name Type Length Min Max Description Index PK FK References
PDID Varchar Physician diagnosis ID x
DiagnosisDate Date Diagnosis date
DiagnosisTime Time Diagnosis time
PhysicianID Varchar Physician ID X?
PatientID Varchar Patient ID X?
DiagnosisCode Varchar Diagnostic code X?
Diagnosis_T
Data Item Value
Name Type Length Min Max Description Index PK FK References
DiagnosisCode Varchar Diagnosis code
DiagnosisName Varchar Diagnosis name
Outpatient_T
Data Item Value
Name Type Length Min Max Description Index PK FK References
OPatientID Varchar 6 Outpatient ID x
Visit_T
Data Item Value
Name Type Length Min Max Description Index PK FK References
VisitNo Varchar 8 Visitor number x
VisitDate Date Visitation date
VisitTime Time Visitation time
VisitReason Varchar 40 Visitation reason
OPatientID Varchar 6 Outpatient ID x
Bed_T
Data Item Value
Name Type Length Min Max Description Index PK FK References
RoomNo Varchar 4 Room number x
BedNo Varchar 1000 Bed number x
Nurse_T
Data Item Value
Name Type Length Min Max Description Index PK FK References
NurseID Varchar 6 Nurse ID
CertDegree Varchar 40 Degree certificate
StateLicenseNo Varchar 40 State license number
NurseSpecialty Varchar 40 Nurse specialty
NurseType Varchar 40 Nurse type
Example Below:
TABLE: Employee_T
Data Item Value
Name Type Length Min Max Description Index PK FK References
EmpID Varchar 5 Person ID Y Y Person(PersonID)
DateHired Date Hire date of employee
EmpType Varchar 1 “N” “T” Subtype discriminator; values N, S, T. N=Nurse, S=Staff, T=Technician

Powered by