An Entity Relationship Diagram ERD is the first step in a databases design. It is useful for building databases because relationships between entities define how key fields link to one another to combine data across tables. An ERD provides a simpler way to control and summarize the complicated data relationship in a relational database.
Different diagram symbols and tables in the ERD are representing different meaning, such as entities, attributes and the relationship between entities.
This table represents an entity with attributes. It means that Entity A have three attributes which are B, C, and D.
The lines with symbolized ends connecting entities are called Crows Feet. It represents the relationship between two tables. The R1 relationship represent that a one to zero or many relationship is between the Entity A and Entity X. It means that for every row in table A there is zero or many corresponding row in table X. The R2 relationship represent that a one to one or many relationship is between the Entity A and Entity X. It means that for every row in table A there is one or many corresponding row in table X. R2 relationship represent that a one to zero or one relationship is between the Entity A and Entity X. It means that for every row in table A there is zero or one corresponding row in table X. R1 relationship represent that a one to one relationship is between the Entity A and Entity X. It means that for every row in table A there is one and only one corresponding row in table X.
Developing an ERD requires an understanding of the system and its components, so I need the analysis of Petcare first. From information of the analysis, I get some key point of data relationship that Petcare held.
All animals have an owner.
Appointments are at particular branches of Petcare.
Appointments keep the information about the animal the appointment is for, the owner of the animal who requested the appointment.
The outcome of the appointment might be a prescription with one or more drugs on it.
Each veterinary doctor works at one branch of Petcare only.
That key information helps me to define the entity and the relationships between entities. And the other detail information helps me to make my preliminary determination of attributes of entities.
In this case, I have identified the requirements from analysis of Petcare. Then I designed an Entity Relationship Diagram (ERD) for the Petcare database system.
ERD for Petcare Database System
From the ERD for Petcare Database System, there are six entities which are Owners, Animals, Branch, Doctors, Appointments and Prescription.
In the Petcare Database System, the relation between Owner and Animals is a one-to-many relationship. It is because an animal always has an owner, while an owner must have at least one pet.
The relation between Branch and Doctors is also a one-to-many relationship. It is because each veterinary doctor works at one branch of Petcare, while a branch must have at least one doctor.
The relation between Appointments and Animals is a one-to-many relationship. It is because an animal should have at least one appointment while an appointment must contain only one animal data.
The relation between Appointments and Owners is redundant. It is because an appointment always contains only one animal, which must have an owner.
The relation between Appointments and Doctors is a one-to-many relationship. It is because a doctor sometimes has one or more appointment while an appointment must contain only one doctor.
The relation between Appointments and Branch is redundant. It is because an appointment always contains only one doctor, which must work in a branch.
The relation between Appointments and Prescription is a one-to-zero or one relationship. It is because an appointment sometimes has none or one prescription while a prescription must referred by an appointment.
The ERD describe the cardinality ratio and participation constraint of each relationship type.
Task 2
After finishing the initial ERD design, we should do Normalization. Normalization can simplify the database structure and make the programming relatively easier.
First Normal Form:
Every non-key attribute in Owners is functionally dependent upon the primary key OID. So its relation is in First Normal Form. The table remains unchanged.
Second Normal Form:
The relation of Owners is in First Normal Form and every non-key attribute in it is fully functionally dependent on the primary key OID. So its relation is in Second Normal Form. The table remains unchanged.
Third Normal Form:
The relation of Owners is in Second Normal Form and every non-key attribute in it is non-transitively dependent on the primary key OID. So its relation is in Third Normal Form. The table remains unchanged.
First Normal Form:
Every non-key attribute in Animals is functionally dependent upon the primary key AID. So its relation is in First Normal Form. The table remains unchanged.
Second Normal Form:
The relation of Animals is in First Normal Form and every non-key attribute in it is fully functionally dependent on the primary key AID. So its relation is in Second Normal Form. The table remains unchanged.
Third Normal Form:
The relation of Animals is in Second Normal Form and every non-key attribute in it is non-transitively dependent on the primary key AID. So its relation is in Third Normal Form. The table remains unchanged.
First Normal Form:
Every non-key attribute in Branch is functionally dependent upon the primary key BName. So its relation is in First Normal Form. The table remains unchanged.
Second Normal Form:
The relation of Branch is in First Normal Form and every non-key attribute in it is fully functionally dependent on the primary key BName. So its relation is in Second Normal Form. The table remains unchanged.
Third Normal Form:
The relation of Branch is in Second Normal Form and every non-key attribute in it is non-transitively dependent on the primary key BName. So its relation is in Third Normal Form. The table remains unchanged.
First Normal Form:
Every non-key attribute in Doctors is functionally dependent upon the primary key DID and SpecType. So its relation is in First Normal Form.
Second Normal Form:
The relation of Doctor and that of Specialism is in First Normal Form. Every non-key attribute in Doctors is fully functionally dependent on the primary key DID. So its relation is in Second Normal Form. There are not any non-key attributes in Specialism. So its relation is in Second Normal Form.
Third Normal Form:
The relation of Doctor and that of Specialism is in Second Normal Form and every non-key attribute in both of them is non-transitively dependent on the primary key BName. So their relations are in Third Normal Form. The tables remain unchanged.
First Normal Form:
Every non-key attribute in Appointments is functionally dependent upon the primary key APID. So its relation is in First Normal Form. The table remains unchanged.
Second Normal Form:
The relation of Appointments is in First Normal Form and every non-key attribute in it is fully functionally dependent on the primary key APID. So its relation is in Second Normal Form. The table remains unchanged.
Third Normal Form:
The relation of Appointments is in Second Normal Form and every non-key attribute in it is non-transitively dependent on the primary key APID. So its relation is in Third Normal Form. The table remains unchanged.
First Normal Form:
Every non-key attribute in Prescription is functionally dependent upon the primary key PID and DrugID. So its relation is in First Normal Form.
Second Normal Form:
The relation of Prescription and that of Processing is in First Normal Form and every non-key attribute in them is fully functionally dependent on the primary key. So its relation is in Second Normal Form.
Third Normal Form:
The relation of Prescription, the relation of Processing and the relation of Drugs are in Second Normal Form. Every non-key attribute in each of them is non-transitively dependent on the their primary key separately. Their relations are in Third Normal Form.
The Normalized ERD for Petcare Database System
This is the normalized ERD for Petcare Database System. All the tables clearly indicating the primary and foreign keys are normalized.
Task 3
I will use MS Access as a Database Management System (DBMS) to setup all of the normalized tables, and populate them with well-designed test data. In the setting up process of the tables, the tables should have different priority. A table associated by another table has relatively higher-priority in the processing.
1. Set up table Owners:
Normalized table Owners with Well-Designed Test Data
OID is used to identify all animal owners. It is the primary key defined as a character string with fixed length 4. OName is the owners name. It is defined as a character string with minimum length 1 and maximum length 40. OAdd is the address of the owner. It is defined as a character string with minimum length 1 and maximum length 80. OH_Tel and OM_Tel are the home telephone number and mobile telephone number of the owner. They are defined as character string with fixed length 8. I assume that the Petcare is in Hong Kong, so its telephone number is base on local telephone number which is a combination of eight integers.
2. Set up table Animals:
Normalized table Animals with Well-Designed Test Data
AID is used to identify all animals. It is the primary key and defined as a character string with fixed length 4. AType is the animal type. It is defined as a character string with minimum length 1 and maximum length 20. Breed is the breed of animals. It is defined as a character string with minimum length 1 and maximum length 20. Age is the age of animal. It is defined as integer. Sex is the sex of animal. It is defined as a character string with fixed length 1. OID is defined as foreign key.
3. Set up table Branch:
Normalized table Branch with Well-Designed Test Data
BName is used to identify the branches. It is the primary key and defined as a character string with minimum length 1 and maximum length 20. BAdd is the address of the branch. It is defined as a character string with minimum length 1 and maximum length 60. Op_Hours is the opening hours of the branch. It is defined as a character string with minimum length 1 and maximum length 20. BTel and EmerTel are the telephone number and emergency contact telephone number of the branch. They are defined as character string with fixed length 8. I assume the telephone number is base on Hong Kong telephone number which is a combination of eight integers.
4. Set up table Doctors:
Normalized table Doctors with Well-Designed Test Data
DID is used to identify all doctors. It is the primary key and defined as a character string with fixed length 4. DName is the name of the doctor. It is defined as a character string with minimum length 1 and maximum length 20. DAdd is the address of doctor. It is defined as a character string with minimum length 1 and maximum length 60. DH_Tel and DM_Tel are the home telephone number and mobile telephone number of the doctor. They are defined as character string with fixed length 8. BName is the foreign key. I assume the telephone number is base on Hong Kong telephone number which is a combination of eight integers.
5. Set up table Specialism:
Normalized table Specialism with Well-Designed Test Data
SpecType is specialism type of doctors. It is the primary key and defined as a character string with minimum length 1 and maximum length 20. DID is the primary key and also foreign key.
6. Set up table Appointments:
Normalized table Appointments with Well-Designed Test Data
APID is used to identify all appointments. It is the primary key and defined as a character string with fixed length 5. Time is the time of the appointment. It is defined as time. Date is the date of appointment. It is defined as date. Diagnosis is the diagnosis of the appointment. It is defined as character string with minimum length 1 and maximum length 60. Charge is the charge of the appointment. It is defined as number. DID and AID are foreign key.
7. Set up table Prescription:
Normalized table Prescription with Well-Designed Test Data
PID is used to identify all prescriptions. It is the primary key and defined as a character string with fixed length 4. APID is the foreign key.
8. Set up table Drugs:
Normalized table Drugs with Well-Designed Test Data
DID is used to identify all drugs. It is the primary key and defined as a character string with fixed length 5. DrugType is the name of drug type. It is defined as a character string with minimum length 1 and maximum length 20. Period is the period of drug. It is defined as a integer. DrugCost is the cost of drug. It is defined as number.
9. Set up table Processing:
Normalized table Processing with Well-Designed Test Data
PID and DrugID are primary keys and also foreign keys.
The process of setting up the normalized tables should be done step by step. It is because some tables have higher-priority. The DBMS (MS Access) would not allow setup the low-priority tables before the high-priority table constructed. Thats why order of setting up tables is important.
Task 4
I have done two queries the question requested by using Structured Query Language (SQL) in MS Access. The results are base on the source data of the tables in Task 3 and have been shown in the following printout.
Query 1:
Query1 constructs a table. The table shows the names (BName) and addresses (BAdd) of the branches of Petcare and the names (DName) of all the veterinary doctors working at each of the branches. The specialism (SpecType) of the veterinary doctors has also been shown.
Query 2:
Query 2 constructs a table. The table shows all the appointments (APID) for the whole of the Petcare organization and those have been ordered by date. The table also shows the branch (BName) the appointment is at, the name (DName) of the veterinary doctor the appointment is with, the date (Data) and time (Time) of the appointment, the name (AID) of the animal the appointment is for, the type (AType) of animal and the breed (Breed) of the animal.
Task 5
In the analyzing, designing and implementing stage of the Petcare Database System above, I have made some assumptions. Those assumptions are directly affecting design of the database system.
In the analyzing stage, I assume that all the information of the initial analysis is relevant and those are in high accuracy. The requirements of the database system are correctly collected. Those assumptions make me clearly understand the daily operation of the Petcare Company. If we can especially built up team for collecting and analyzing information about the database system, we can make an accurate analysis before design ERD.
In the designing stage, I assume that the relevant analysis is the only source of data for designing the database system. And the data flow of the database system base on Petcare Company and it is correctly designed. Those assumptions make sure the ERD design and data flow design of the system are fulfill the expectations of the customers.
In the implementing stage, I assume that all the data type of the database system is well defined and the relations of data correctly defined. Those assumptions directly affect the structure of the building up database system.
If I am as knowledgeable about Petcare Company as a manager of that compay, find a person around me who does know something about it. Use it as an opportunity to learn about developer/user interactions. I am the developer and they tell me what should be in the system. I can use the ERD as a discussion model to verify the correctness of my design in each stage.
