Entity Marriage Data Model And Normalization Computer Knowledge Essay

Draw an Entity Romantic relationship Data Model that identifies the content and composition of the info kept by Petcare. Designate the cardinality percentage and contribution constraint of every romance type.

Answer:

Draw an Entity Romance Data Model

To design the database system for Petcare, we need to bring and Entity Marriage Diagram showing all and entities and the partnership between the enteritis. The ERD models the static human relationships amongst these details that are preserved and managed by the data source. The ERD shows how items of data relate, statically, to each other.

1. 1. 1 Entities of the ERD

First step, we need to specify the entities in the databases.

According the necessity of the Petcare, the below entities should be engaged in the repository system to record the Petcare's information;

- Information of your pet included the sort, breed, sex, era and their owner

- Information of your pet owner included their name, address, home telephone number and mobile telephone number

- Information included the pet the appointment is perfect for, the owner of the pet, this branches of Petcare, the attending veterinary Veterinarian and the time frame and time of the session.

- Information of the veterinary Vet included their name, address, home telephone number, mobile telephone number, which branch they report to and their focus.

- The results of the session which included the prescription and the charge made.

1. 1. 2 Entities relationship

Next step is to establish the relationship of each entity. The partnership included Involvement Constraint and Cardinality Percentage.

Participation Constraint

There are two types of contribution constraint known as 'Total' and 'Incomplete' contribution.

A 'Total' contribution (mandatory involvement) constraint specifies that each member in the entity must get involved as a member of another linked entity in the generalization.

A 'Incomplete' involvement (optional contribution) constraint specifies a member of the entity will not participate in any member of another connected entity in the generalization.

Cardinality Ratio

Three types of cardinality is referred to as below

One-to-one (1:1) - Relationship between two entities which is connected each other with the principal key(s) only.

Many-to-many (M:N) - Relationship between two entities which is connected each other with no primary key. It also have the opportunity to create the redundancy problem.

Therefore it is best to break right into two relationships such as one-to-many and many-to-one.

One-to-many (1:M) or Many-to-one (M:1) - Connection between two entities which is linked the other person from the primary key(s) to overseas key(s).

The marriage of Dog or cat - Your pet was raised by the owner; each pet should have only 1 owner but the owner can keep more then 1 family pet. The owner can also keep difference kind of pets. There fore, the relationship of Family pet and Owner is Total and someone to many.

Relationship of Owner - The pet owner would make appointment with the Veterinarian. In the true case, an owner would make more then 1 appointment, also the Vet also not manage 1 circumstance only. A relational repository is not allowed this condition. To solve the issue another entity 'Visit' was added between Owner and Animal medical practitioner. The Owner could make the visit before visit the veterinarian, also in addition they can walk-in but without visit. In order to simple the databases design, we see the walk-in also as a fresh appointment. Therefore, the relationship of Owner and Visit is Total and one to many.

Relationship of Appointment - the visit was created by the pet owner and was taken care of be the Vet. However, some Vet may respond to the special case and would not handle the visit. Therefore, the partnership of Visit and Vet is Partial and many to 1 relationship.

Relationship of Vet - Over the above we already mentioned a Veterinarian would enroll in more the other session. Also the Veterinarian would issue more then 1 diagnosis. Diagnosis is the results of the visit which was issued by the vet. The partnership of Animal medical practitioner and Examination is Total and someone to many.

1. 1. 3 Entity Relationship Diagram (ERD)

According the aforementioned entities and romantic relationship design we acquired the first ERD as below; The ERD was confirmed the logical database design of the Petcare. The ERD recognizes a set of data elements that must be included in the database.

Fig. 1. The ERD of Petcare.

Task 2

Produce the resulting normalized tables obviously indicating the principal and foreign keys.

Answer:

Normalization

After complete the reasonable design of the database and get the ERD, we can begin the physical design of the data source. The first thing is to set-up the table and explain the data-set need to store in the databases. A good databases design should make this content of each desk is merely related to its own entity. In order to meet this necessity, normalization process should be applied.

The normalization process needs detail by detail to change the data source and each step have specify name, called;

Un-normal Form - UNF

First Normal Form - 1NF

Second Normal Form - 2NF

Third Normal Form - 3NF

Boyce-Codd Normal Form - BCNF

The below steps should be carried out for the normalization.

Collect the data-set

Transform the un-normalized form desks into first normal form

Transform the first normal form dining tables to second normal form

Transform the next normal form furniture to third normal form

First Normal Form (1NF)

According the ERD design and the necessity of Petcare, the Furniture of the databases were created as below; also some attributes were put into indentify the data-sets, e. g. PetID, which features also the Primary of the desk.

Fig. 2. The Un-normal Form table

The above furniture were included all requested data and information wanted by Petcare. However, some of attributes are the same in difference tables. The desks are a un-normal form table. We have to make this content of each desk is merely related to its entity. Therefore the normalization process should be carried out.

The first step is changing the un-nornalized form table into first normal form. The definition of first normal form is 'all non-key features in relationship is if, and only if, functionally dependent after primary key.

According the definition of first normal form, we ought to avulse the non-key traits that are not relate to the principal key and create another desk for this feature.

For example; in the table Veterinarian, the Branch and Specialise aren't related to the principal key, so we are in need of avulse these characteristics and create another table, Branch & Specialise. Another stand Medical diagnosis also the same issue.

Fig. 3. Normalized table

Fig. 4 Normalized stand 2

After the procedure of first normalized, we acquired the First Normal Form table as below;

Fig. 5. First Normal Form table

Second Normal Form (2NF)

The second step is transfer first normal form to second normal form. This is of second normal form is 'all relationships are, and only if, it is within first normal form and every non-key attribute are totally functionally dependent on the primary key.

After second normalization, we got the below dining tables;

Fig. 6. Second Normalized tables

Third Normal Form (3NF)

The meaning of third normal form is 'all relations are if, and only if, it is in second normal form and every non-key characteristics in nor-transitively dependent on the primary key. Because the second normalized furniture already meet up with the equipment. Therefore, it is third normal form.

Primary Key and Foreign Key

The next step to create the physical databases is defining the Primary Key and the Overseas key. This task also defining the partnership of each stand. THE PRINCIPAL Key can be used to identify the record in each desk. The Foreign Key is the main element attribute to web page link other table.

The final normalized table with details romantic relationship, Key key and Foreign key were revealed on the below diagram.

Fig. 7. Final table design

Data Dictionary

The last step of design the database is define the house of each feature. The house of attribute is design the coding of the feature. A good coding design is very important because a good coding design can let use easy to comprehend and take less mistake when input the info, and also identified the usability and extensibility of the data source.

A good coding design should meet below requirements;

Uniqueness - only one value of the code may be employed to a entity.

Expandability - must have sufficient space for the admittance of new items.

Conciseness - should use the least number of character types to identify each item.

Uniform size & format - no addition of prefixes or suffixes.

Simplicity - simple to apply and understand.

Versatility - easy to modify for changes in conditions.

Sortability - may not be the code itself so long as it can be correlated with a sortable code.

Stability - shouldn't need regular updating as this is costly.

Meaningful - in a few circumstances it is useful for the code to echo the characteristics of the coded entities.

Operability - the code should be sufficient to meet the current and future needs of data id but also minimise the clerical and system resources required to operate it.

Other then your coding design, the series of table create also very important as the desk may has connection with other dining tables. We should first create the table which has not relate with other dining tables, then create the table relate with this table. The sequence of table create list as the collection of the Data dictionary.

According these requirements, the desks and capabilities were created and the details were listed on the Data Dictionary below;

Data Dictionary of Petcard

Table Name

Field Name

Type

Size

Status

Description

Owner

OwnerID

char

6

PK

The ID of owner

O_Name

char

30

Not NULL

Name of owner

O_Address

varchar

100

NULL

Address of owner

O_Phone

char

15

NULL

Phone variety of owner

O_Mobile

char

15

NULL

Mobile quantity of owner

PetType

TypeID

char

1

PK

The ID of pet type

P_Type

char

15

Not NULL

name of family pet type

Breed

BreedID

char

2

PK

The Identification of breed type

B_type

char

15

Not NULL

Name of Breed type

TypeID

char

1

FK

relate to PetType

Pet

PetID

char

6

PK

The Identification of the Pet

OwnerID

char

6

FK

relate to Owner

PetName

char

20

Not NULL

Name of the pet

Sex

char

1

Not NULL

sex of the pet

Age

numeric

2

Not NULL

age of the pet

Breedid

char

2

FK

relate to Breed

Branch

BranchID

char

1

PK

The Identification of branch

B_Name

char

15

Not NULL

Name of branch

B_Address

char

50

Not NULL

Address of branch

B_Phone

char

15

Not NULL

Phone number of branch

Specialisation

SpeID

char

2

PK

The ID of specialize

Specialise

char

10

Not NULL

Name of the specialization

Veterinarian

VetID

char

3

PK

The ID of veterinaria

V_Name

char

30

Not NULL

Name of veterinaria

V_Address

varchar

100

Not NULL

Address of veterinaria

V-Phone

char

15

Not NULL

Phone variety of veterinaria

V_Mobile

char

15

Not NULL

Mobile volume of veterinaria

SpecID

char

2

FK

relate to Specialisation

BranchID

char

1

Not NULL

The branch of the veterinaria is working

Appointment

AppID

char

10

PK

The Identification of appointment

VetID

char

3

FK

relate to Veterinarian

OwnerID

char

6

FK

relate to owner

BranchID

char

1

FK

relate to Branch

PetID

char

6

FK

relate to Pet

Date

date

8

Not NULL

date of appointment

Time

time

4

Not NULL

time of appointment

Drug Type

D_TypeID

char

2

PK

The Identification of Drug Type

D_Type

char

20

Not NULL

Name of medicine type

Drug

DrugID

char

4

PK

The Identification of drug

D_typeID

char

2

FK

relate to DrugType

DrugName

char

30

Not NULL

Name of the drug

Category

char

20

Not NULL

How to make use of the drug

Period

char

2

Not NULL

Number of day to make use of the drug

Prescription

PrescID

char

10

PK

The ID of Prescription

Drug1

char

4

FK

relate to Drug

Drug2

char

4

FK

relate to Drug

Drug3

char

4

FK

relate to Drug

Diagnosis

DiagID

char

6

PK

The Identification of diagnosis

VelID

char

3

FK

relate to Velerinarian

PrescID

char

10

FK

relate to Prescription

Price

Numeric

6

Not NULL

The price of diagnosis

Task 3

Using a Repository Management System (DBMS) set-up all the above normalized dining tables, and populate them with well-designed test data. .

Answer:

Create desks on DBMS

After completely designed the data source, we should start to create it. Regarding to acceptance and simple to use, we determined to work with Microsoft Access as the Database Management System (DBMS) of the Petcare data source. We have to use SQL command to generate the tables, qualities and human relationships of desks.

Create tables

The SQL command of create tables was lists below;

Create the stand Owner

CREATE TABLE Owner

(OwnerID CHAR(6) Key KEY,

O_Name CHAR(30),

O_Address VARCHAR(100),

O_Phone CHAR(15),

O_Mobile CHAR(15) );

Create the table PetType

CREATE Desk PetType

(TypeID CHAR(1) PRIMARY KEY,

P_Type CHAR(15) );

Create the table Breed

CREATE Stand Breed

(BreedID CHAR(2) Main KEY,

B_type CHAR(15),

TypeID CHAR(1) REFERENCES PetType(TypeID) ON DELETE CASCADE ON UPDATE CASCADE );

Create the table PetType

CREATE Stand Pet

(PetID CHAR(6) Principal KEY,

OwnerID CHAR(6) Recommendations Owner(OwnerID) ON DELETE CASCADE ON Upgrade CASCADE,

PetName CHAR(20),

Sex CHAR(1),

Age NUMERIC(2)

BreedID CHAR(2) Referrals Breed(BreedID) ON DELETE CASCADE ON UPDATE CASCADE );

All the tables also used the similar SQL demand to create, we would not do it again all there. After all tables, traits and associations were created. The complete desk diagram was revealed as below physique;.

Fig. 8. Furniture in MS Access

Test Data

After the stand creation finished. We should input the test data to the repository to check the data source functioned as expended. We would source at least 5 information to each table.

The below display revealed the test data of most tables;

Fig. 9. Table Owner, Family pet, PetType, Breed

Fig. 10. Desk Veterinarian, Specialization, Session, Branch

Fig. 11. Table Prescription, Diagnosis, Medication, DrugType

The Printout of tables please refers to appendix.

Task 4

Set-up and test all the following questions using Structured Query Words (SQL).

Answer:

Set-up test queries

In order to check the repository design and data type correct, we have to query the data form the database. The below 2 circumstance can test the databases meet the Petcare's requirements.

Query 1

Requirement:

Display the brands and addresses of the branches of Petcare and the brands of all the veterinary doctors working at each one of the branches. Any specialism(s) of the veterinary doctors should also be shown.

This is an extremely basic query in databases, to goal the requirement we need to the be a part of the Branch, Vet and Field of expertise 3 tables and choose the wanted data. The query statement and consequence were listed below;

SELECT

Branch. b_name AS Branch,

Branch. b_address AS Address,

Veterinarian. v_name AS VetName,

Specialization. specialise AS Specialization

FROM

(Branch INNER JOIN Veterinarian ON Branch. branchid=Veterinarian. branchid)

INNER JOIN Specialization ON Vet. SpecID=Expertise. SpecID

ORDER BY Branch. b_name;

Fig. 12. Result of Query 1.

Query 2

Requirement:

Display all the meetings for your of the Petcare group. This will be bought by date. The result should screen the branch the session is at, the name of the veterinary doctor the appointment is with, the date and time of the visit, the name of the animal the appointment is made for, the type of creature and the variety of the pet.

Case two is more difficulty; we need to join Session, Branch, Veterinary, Pet, PetType and Breed total 6 tables. The query is highly complex and easy to make problem. The query statement and query results were exhibited below;

SELECT

Branch. b_name AS Branch,

Veterinarian. v_name AS VetName,

Appointment. time frame,

Appointment. time,

Pet. petname AS PetName,

PetType. P_Type AS Type,

Breed. breed AS Breed

FROM

((((Session INNER JOIN Veterinarian ON Visit. vetid=Animal medical practitioner. vetid)

INNER Be a part of Branch ON Animal medical practitioner. branchid=Branch. branchid)

INNER JOIN Pet ON Session. petid=Family pet. petid)

INNER Sign up for Breed ON Dog or cat. breedid=Breed. breedid)

INNER JOIN PetType ON Breed. TypeID=PetType. TypeID)

ORDER BY Session. date;

Fig. 13. Result of query 2

Task 5

Explain any assumptions you have made when analyzing, making and implementing the above mentioned database. .

Answer:

Assumption and alternative

Each appointment Identification is for one dog or cat only. Same owner bring more than 1 dogs to Petcare whould see as a fresh appointment.

One prescription maximum list 3 drugs. Normally 3 drugs are enough for 1 circumstance. If your pet has more than 1 issue need more then 3 drugs, veterinary should open multi prescription for difference kind of drugs. E. g. need pores and skin medication & Painkiller.

Walk-in also considers as a new appointment.

Work Improvement

In this project, the databases system for PatCare has not included an individual interface. The data source system can be used for store data and the DBMS can be used for supervisor the databases or it can be views as a bridge of Repository and User interface. DBMS is not created for customer, it mainly use command line range to manager the database and query the info. Even some new DBMS; system such as MS Access and SQL have GUI but also hard for a standard user to work with the databases and query data.

In order to let users easy to use the database, INTERFACE is crucial. The user user interface let customer easy to type data, update details and check the records. And in addition we can per-set some varieties and reports for user print.

The user interface not want very stunning or complex. An excellent user interface is simple, convenient and easy to comprehend and simple to use. We ought to design an online base interface because the web base interface is simple and easy to manage. The most important is the web base interface does not need install additional programs, therefore not additional budget for buy other applications.

Also We Can Offer!

Ошибка в функции вывода объектов.