Entity Relationship Data Model Content And Structure Computer Science Essay

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Task 1

Draw an entity Relationship Data Model That describes the content and structure of the data held by Petcare. Specify the cardinality ratio and participation constraint of each relationship type.

Introduction

In task 1, to draw an entity relationship data model that describes the content and structure of the data held by pet care. This model will describe a full relationship between data.

Entity

An entity is something that has a distinct, separate existence, although it doesn't need to be a material existence. In particular, abstractions and legal fictions are also usually regarded as entities. Basically there is also not any presumption that an entity is animate. All Entities are used in system developmental models where display communications and internal processing of documents compared to order processing.

An entity can be viewed as a set containing subsets. In philosophy, such sets are said to be abstract objects.

All Entities have attributes. All Attributes are data and those data describes or qualifies the entity.

Relationship

In Relationship between entities describes the where all occurrences of one entity links another entity or influences the occurrence of another entity. Or in other way a relationship exists between two database tables when there is one table has a foreign key that references the primary key of another table.

Entity Relationship Model

An entity relationship modeling is a method of database modeling and which is used to produce conceptual schema or semantic data model of an existing or developing system. Where Diagrams created with using this following methods are called ERD (Entity Relationship Diagram).

An Entity Relationship Diagram (ERD) is a conceptual design of the entities that exist in a system and relationships between those entities. ERD is often used to visualize a relational database.

Selecting initial entities

Analyzing the scenario I found following major entities:

Breed

Animal types

Owner

Appointment

Doctors

Branches

Drug Type

Prescription

Entity Relationship Data model

Branches

Branch_ID (Pk)

Branch_Name

Branch_Address

Branch_Contract No

Branch_Opening Hour

Drugs Type

Drug Type_No (Fk)

Drug Type_Name

Drug_Id (Pk)

Drug_Name

DrugsAnimal type

Animal Type No (Fk)

Animal Type Name

Owner

Owner_ID (Pk)

Owner_Name

Owner_Address

Owner_Contruct Number

Prescription

Prescription_No (Pk)

Prescription_Date

Prescription_Time

Breeds

Breed_ID (Pk)

Dog_breed

Cat_breed

Rabbit_breed

Animal

Animal_ID (Pk)

Animal_Name

Animal_Sex

Animal_Age

Appointment

Appointment_No (Pk)

Appointment_Date

Appointment_Time

Doctor_ID (Pk)

Doctor_Name

Doctor_Address

Doctor_Contract Address

Doctor_Specialise area

DOCTOR

Relationship

Doctor

BranchRelationship between (Branch↔Doctor) - Every veterinary surgery branch has many doctors. So that's why it is One to many relationship.

Relationship between (Branch ↔ Animal) - In Every veterinary surgery branch one or more Animal came to take treatments. It is One to many relationship.

Animal

Branch

Relationship between (Doctor ↔Appointment) -One veterinary doctor can take one or more animal appointment. So this relationship is one to many relationships.

Doctor

Appointment

Animal

Appointment

Relationship between (Animal ↔ Appointment) - There could be many animals can be appointment each veterinary branch. So that's why it is one to many relationships.

Owner

Animal

Relationship between (Owner ↔ Animal) - Every animal owner has one or more animal. So that's why it is one to many relationships.

Relationship between (Breeds ↔ Animal) - One animal breeds has deferent animal. That's why it is one to many relationships.

Breeds

Animal

Relationship between (Animal type↔ Breeds) - One animal type has many breeds. So it is one to many relationships.

Animal type

Breeds

Appointment

Prescription

Relationship between (Appointment ↔ Prescription) -One appointment animal veterinary give one or more prescription. So it is one to many relationships.

Relationship between (Prescription ↔ Drugs) - One prescription purpose doctor give the animal one or more drug. So it is one to many relationships.

Prescription

Drugs

Relationship between (Drug type ↔ Drug) - One type drug has different of drug. That's why it is one to many relationships.

Drug type

Drug

Conclusion

In this Task 1 the Following Items are Discussed They are entity relationship data Model and there Relation between Them. Following to the task 1 proposal each of task discussed to Every Single Relationships.

Task 2

Produce the resulting normalized tables clearly indicating the primary and foreign key.

Introduction

In the task 2 have to produce the resulting normalized tables clearly indicating the primary and foreign keys.

Normalization

Normalization describes the process of organizing tables in a way that the results of using the database are always unambiguous and as intended. Where Normalization may be responsible for the effect of duplicating data within the database and it is also often results in the creation of additional tables. (While normalization tends to increase the duplication of data, it does not introduce redundancy, which is unnecessary duplication.) Normalization is a technique for designing relational database tables and minimizing each redundancy and safeguarding the database from anomalies. For example when multiple instances of a piece of data occur in a table, the possibility of that data exists that these instances will not be kept consistent when updating the data in the table.

So Normalization is typically a refinement process after the initial exercise of identifying the data entities and that should be in the database, identifying their relationships, and defining the tables required and every column within each table.

To produce a set of normalized tables below following steps need to be followed in sequence.

1. Gathering data

2. Choosing a key

3. Converting to first normal form

4. Converting to second normal form

5. Converting to third normal form

6. Optimization

Resulting normalized tables clearly indicating the primary key and foreign key

Gathering Data:

Veterinary

Branch

Branch ID

Branch Name

Branch Address

Branch Telephone Number

Emergency Telephone Number

Branch opening hour

Branch age

Branch number

Branch position

Animal

Animal ID

Animal name

Animal type

Animal sex

Animal age

Animal number

Animal breeds

Breeds name

Dog breed

Cat Breed

Rabbit Breed

Breeds id

Animal owner

Owner name

Owner id

Owner address

Owner contract address

Owner age

Owner sex

Appointment no

Appointment date

Appointment time

Appointment doctor name

Prescription no

Prescription date

Prescription doctor name

Prescription time

Drug

Drug no

Drug name

Drug type

Drug date

Veterinary doctor

Doctor name

Doctor id

Doctor address

Doctor mobile number

Doctor telephone number

Doctor Specialize area

Doctor sex

Doctor age

Treatment

Diagnosis

Converting to First Normal Form

Veterinary

Branch

Branch ID (PK)

Animal

Animal ID

Breeds name

Breeds id

Owner name

Owner id

Appointment no

Appointment date

Prescription no

Prescription date

Drug no

Drug name

Doctor name

Doctor id

Treatment

Diagnosis

BRANCH

Branch _ID (PK)

Branch _Name

Branch age

Branch number

ANIMAL

Animal _ID (PK)

Animal _Name

Animal age

Animal number

BREEDS

Breeds _ID (PK)

Breeds _Name

ANIMAL TYPE

Animal type_ID (PK)

Animal type_Name

OWNER

Owner _ID (PK)

Owner _Name

APPOINTMENT

Appointment_no (PK)

Appointment_ Doctor Name

PRESCRIPTION

Prescription_no (PK)

Appointment_ Doctor Name

DRUG

Drug_ID (PK)

Drug_ Name

DRUG TYPE

Drug type_ID (PK)

Drug Type_ Name

DOCTOR

Doctor _ID (PK)

Doctor _Name

Converting to Second Normal Form

Veterinary

Branch

Branch ID (PK)

Branch _Age

Animal

Animal ID

Breeds name

Breeds id

Owner name

Owner id

Appointment no

Appointment date

Prescription no

Prescription date

Drug no

Drug name

Doctor name

Doctor id

Treatment

Diagnosis

BRANCH

Branch _ID (PK)

Branch _Name

Branch _Age

ANIMAL

Animal _ID (PK)

Animal _Name

Animal _Age

BREEDS

Breeds _ID (PK)

Dog_breed

Cat_breed

Rabbit_breed

ANIMAL TYPE

Animal type_ID (FK)

Animal type_Name

OWNER

Owner _ID (PK)

Owner _Name

Owner _Age

Owner _Address

Owner _Contract Number

APPOINTMENT

Appointment_no (PK)

Appointment_ Date

Appointment_ Time

PRESCRIPTION

Prescription_no (PK)

Appointment_ Date

Appointment_ Time

DRUG

Drug_ID (PK)

Drug_ Name

DRUG TYPE

Drug type_ID (FK)

Drug Type_ Name

DOCTOR

Doctor _ID (PK)

Doctor _Name

Doctor _Age

Converting to Third Normal Form

BRANCH

Branch _ID (PK)

Branch _Name

Branch _Age

Branch _Address

Branch_Telephone Number

Branch_Opening Hour

In the Branch table branch_ID is Primary key and there is no a Foreign key in this table.

DOCTOR

Doctor _ID (PK)

Doctor _Name

Doctor _Age

Doctor _Address

Doctor_Specialise area

Doctor _Contract Number

Branch _ID (FK)

In the Doctor Table doctor_ID is Primary key and there are Brance_Id, is a foreign key.

ANIMAL

Animal _ID (PK)

Animal _Name

Animal _Age

Animal _Sex

Branch _ID (FK)

Owner _ID (FK)

Breeds _ID (FK)

Appointment_no (Fk)

In the Animal Table Animal_ID is Primary key and Brance_Id, Owner_Id, Breeds_Id, Appoointment_No is a foreign key.

BREEDS

Breeds _ID (PK)

Dog_breed

Cat_breed

Rabbit_breed

Animal type_ID (FK)

In the Breeds Table Breeds_ID is Primary key and there are Animal type_Id _is a foreign key.

ANIMAL TYPE

Animal type_ID (FK)

Animal type_Name

In this Animal type Table Animal type _ID is foreign key and there is no Primary key in this table.

OWNER

Owner _ID (PK)

Owner _Name

Owner _Age

Owner _Address

Owner _Contract Number

In the Owner Table Owner _ID is Primary key and there is no a foreign key in this table.

APPOINTMENT

Appointment_no (PK)

Appointment_ Date

Appointment_ Time

Doctor _ID (FK)

Animal _ID (FK)

In the Appointment Table Appointment _No is a Primary key and there are Doctor_Id, Animal_Id is a foreign key.

PRESCRIPTION

Prescription_no (PK)

Appointment_ Date

Appointment_ Time

Appointment_no (FK)

In the Prescription Table Prescription_no is Primary key and there are Appointment _No, is a foreign key.

DRUG

Drug_ID (PK)

Drug_ Name

Prescription_no (FK)

Drug type_ID (FK)

In the Drug Table Drug_ID is Primary key and there is a Drug type_ID, Prescription_no is a foreign key.

DRUG TYPE

Drug type_ID (FK)

Drug Type_ Name

In the Drug type Table Drug_ID is foreign key and There is no a Primary key in this table.

Conclusion

In task 2 there have been produce the resulting normalized tables clearly indicates the primary and foreign keys. I have completed the task with proper requirement.

Task 3

Using a database Management System (DBMS) of your Choice, set up all of the above normalized tables and populate them with well designed test data (minimum 5 records per table). Provide Print outs of all tables.

Introduction

In this task 3, to set up all of the normalized tables using MS access and populate them with well-designed test data and providing printouts of all tables.

Animal

C:\Documents and Settings\Jessy M Gomes\Local Settings\Temporary Internet Files\Content.Word\New Picture (8).bmp

Figure 1

Table 1 Animal- In the Animal table we can see the information. This information is - Animal_id, animal_Name, Animal_Sex, Animal_Age, Branch_ID, Owner_ID, Appointment_number, Breed Type.

This information is entered in the database system for veterinary surgery database.

Animal Type

C:\Documents and Settings\Jessy M Gomes\Local Settings\Temporary Internet Files\Content.Word\New Picture (9).bmp

Figure 2

Table 2 Animal Type - In this table the animal type will insert when an animal is going to take treatment in veterinary surgery. In this table we have information - animal_type _ID and Animal_type_Name.

Appointment

C:\Documents and Settings\Jessy M Gomes\Local Settings\Temporary Internet Files\Content.Word\New Picture (10).bmp

Figure 3

Table 3 Appointment - In the appointment table we can see the information which taken by veterinary surgery database system. The following information is - Appointment_number, Appointment_date, appointment_time, doctor_ID, Animal_ID.

Branch

C:\Documents and Settings\Jessy M Gomes\Local Settings\Temporary Internet Files\Content.Word\New Picture (11).bmp

Figure 4

Table 4 Branch - In the branch table there are information about veterinary surgery. This information is - Branch_ID, Branch_Name, Branch_Address, Branch_Telephone_Number, and Branch_opining_hour.

Breed

C:\Documents and Settings\Jessy M Gomes\Local Settings\Temporary Internet Files\Content.Word\New Picture (12).bmp

Figure 5

Table 5 Breed - In the breed table we can see the information about breed. This information's are - Breed_type, Number_Of_Animal, and Animal_Type_ID.

Doctor

C:\Documents and Settings\Jessy M Gomes\Local Settings\Temporary Internet Files\Content.Word\New Picture (13).bmp

Figure 6

Table 6 Doctor - In the doctor there are information about the doctor for veterinary surgery. Following information's are - Doctor_ID, Doctor_Name, Doctor_Addres, Doctor_Contruct_number, Branch_ID, specialization_area.

Drug

C:\Documents and Settings\Jessy M Gomes\Local Settings\Temporary Internet Files\Content.Word\New Picture (14).bmp

Figure 7

Table 7 Drug - In the Drug table we can see the Information about Drugs. The following Information's are - Drug_ID, Drug_Name.

Owner

C:\Documents and Settings\Jessy M Gomes\Local Settings\Temporary Internet Files\Content.Word\New Picture (15).bmp

Figure 8

Table 8 Owner - In this table we can see the information about Owner. The information's are - Owner_ID, Owner_name, Owner_Address, and Owner_Contruct_number.

Prescription

C:\Documents and Settings\Jessy M Gomes\Local Settings\Temporary Internet Files\Content.Word\New Picture (16).bmp

Figure 9

Table 9 Prescription - in the table prescription we can see the information about the prescription which is used to book appointment. In the prescription following information's are - Prescription_Number, Prescription_Date, Precription_Time.

Conclusion

In the task 3, complete the tables with proper DBMS of my choice. According to the task proposal I have completed and set up all of the normalized tables and populate them with designed data.

Task 4

Setup and test all of the following queries using structured Query Language (SQL). Provide printouts of SQL code for each query and the output produced when you run the query in the database you have developed:

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

Display all the appointments for the whole of the Petcare organization. This should be ordered by date. The result should display the branch the appointment is at, the name of the veterinary doctor the appointment is with, the date and time of the appointment, the name of the animal the appointment is for, the type of animal and the breed of the animal.

Introduction

In the task 4, to complete 2 tasks there are 2 databases have to be develop with specific Information. In this also I am going to show the SQL code for each query. Two of the tasks are -

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

Display all the appointments for the whole of the Petcare organization. This should be ordered by date. The result should display the branch the appointment is at, the name of the veterinary doctor the appointment is with, the date and time of the appointment, the name of the animal the appointment is for, the type of animal and the breed of the animal.

At the next page I am going to start the following Queries.

At first I am going to display the names and address of the branches of Petcare and the names of all the veterinary doctors working at each of the branches. Any specialism(s) of the veterinary doctors should also be shown.

The names and address of the branches of the Petcare and the names of all veterinary Doctors. Any Specialism Area.

C:\Documents and Settings\Jessy M Gomes\Local Settings\Temporary Internet Files\Content.Word\New Picture (2).bmp

Figure 10

Below I am going to show the SQL codes For Following queries.

SELECT Branch.Branch_Name, Branch.Branch_Address, Doctor.Doctor_Name, Doctor.Specialization_Area

FROM Branch INNER JOIN Doctor ON Branch.Branch_ID = Doctor.Branch_ID;

C:\Documents and Settings\Jessy M Gomes\Local Settings\Temporary Internet Files\Content.Word\New Picture (4).bmp

Figure 11

In the Second step I am going to Display all the appointments for the whole of the Petcare organization. This should be ordered by date. The result should display the branch the appointment is at, the name of the veterinary doctor the appointment is with, the date and time of the appointment, the name of the animal the appointment is for, the type of animal and the breed of the animal.

Below I am going to show the database for all the appointments for the whole of the Petcare organization.

C:\Documents and Settings\Jessy M Gomes\Local Settings\Temporary Internet Files\Content.Word\New Picture (5).bmp

Figure 12

Display the SQL Code for the following Table

SELECT Branch.[Branch_Name], Doctor.[Doctor_Name], Appointment.[Appointment_Date], Appointment.[Appointment_Time], Animal.[Animal_Name], Breed.[Breed_Type], [Animal Type].[Animal_Type_Name]

FROM ((Doctor INNER JOIN Branch ON Doctor.[Branch_ID]=Branch.[Branch_ID]) INNER JOIN Appointment ON Doctor.Doctor_ID=Appointment.[Doctor_ID]) INNER JOIN ((Animal INNER JOIN Breed ON Animal.[Breed_Type]=Breed.[Breed_Type]) INNER JOIN [Animal Type] ON Breed.[Animal_Type_ID]=[Animal Type].[Animal_Type_ID]) ON Appointment.[Animal_ID]=Animal.[Animal_ID]

ORDER BY Appointment.[Appointment_Date];

C:\Documents and Settings\Jessy M Gomes\Local Settings\Temporary Internet Files\Content.Word\New Picture (5).bmp

Figure 13

Conclusion

This is the Whole Database Inquires That Is proposed in the task 4. Using Microsoft Access I have create the Database. But the database system can be Develop with other Software.

Task 5

Explain any assumptions you have made when analyzing, designing and implementing the above database, justify the approach you have taken and explain any alternative approaches you could have taken to any of the above tasks. Discuss any changes you would make to improve your work.

Introduction

In this task explain the all alternative Way that is used to complete this Documentation. Also the way of the system that can handle the future upgrading. This will demonstrate all of the tasks.

This following system can developed in different ways. In this assignment the database is developed in MS Access. But as for security, more reliable and sensitive use the database can be developed in other way.

In this assignment I have used ERD (Entity Relationship Diagram) which is an entity relationship modeling is a method of database modeling and which is used to produce conceptual schema or semantic data model of an existing or developing system.

Another method is used for normalizing the data. Normalization is typically a refinement process after the initial exercise of identifying the data entities and that should be in the database, identifying their relationships, and defining the tables required and every column within each table.

Veterinary Surgery Database can develop with Other Secure and More complex Software. As for now it is developed the system in normal easy way with Microsoft access. This database can be used as a front-end. With PHP, ASP, JSP, PERL In many web site.

Below I am going to give the references that I have used during my assignment Work.

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.