Database Design & Development

Only available on StudyMode
  • Download(s) : 172
  • Published : November 7, 2011
Open Document
Text Preview
MODULE:
DATABASE DESIGN & DEVELOPMENT

ASSIGNMENT TITLE:
VETERINARY SURGERY DATABASE DEVELOPMENT

Table of Content
Task 1Page 2
Task 2Page 5
Task 3Page 14
Task 4Page 28
Task 5Page 30
ReferencePage 32
BibliographyPage 33
Task 1

Question:
AIM: 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.

Answer:
I have prepared this assignment to demonstrate an understanding of applying data modeling and analysis techniques to the design and development of database solutions. I am as a sub-contracted to design the database system supporting the Veterinary Doctor for the Petcare. Entity relationship Model: a data model originally had three primary constructs: entity, relationship and attributes.

There are three primary constructs in ER models:
• Entities are the "things" about which we seek information. • Attributes are the data we collect about the entities. • Relationships provide the structure needed to draw information from multiple entities. [pic]

Figure1 is shown the entity relationship diagram for the “Petcare”. [pic]
Figure 2 is shown the entity relationship diagram showing primary key, foreign key and attribute. The diagrams in this figure specify the different set of data requirements, namely... An Animal must be only one of Animal Type.

The relation ship Between the Animal and Animal Type entity there is an 1:M An Animal is owned by one Owner.
The relation ship Between the Animal and Owner entity there is an 1:M An Appointment contains a number of Animal.
The relation ship Between the Appointment and Animal entity there is an 1:M An Appointment contains a number of Veterinary Doctor.
The relation ship Between the Appointment and Veterinary Doctor entity there is an 1:M An Veterinary Doctor works one of Branch.
The relation ship Between the Veterinary Doctor and Branch entity there is an 1:1 One Appointment only has one Prescription.
The relation ship Between the Appointment and Prescription entity there is an 1:1 A Prescription contains a number of Drug Type.
The relation ship Between the Prescription and Drug Type entity there is an 1:M ‘In general, when there are loops in your entity relationship diagram, be on the lookout for the possibility of breaking the loop at some point by removing a relationship type that can be synthesized from the composition of other relationship types on the diagram. This is often not possible because of the nature of the relationships.’ { Chapter 3: Entity Relationship Modelling http://www.databasedesign.co.uk/bookdatabasesafirstcourse/chap3/chap3.htm } Task 2

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

Answer:
Primary key: a key or identifier is an attribute which uniquely identifies a single row within the relation. This key often called the primary key. The key should always be the minimum set of attributes required to identify by (PK) Foreign key: An attribute which is the primary key of one relation but appears as an attributes in another relation is called a foreign key. Foreign key is identified by (FK) [pic]

Before Normalization, I need to prepare all ERD and assume some data into the each table. It will be help me to think and handle for the normalizes: Animal Type table:
[pic]
Animal table:
[pic]
Owner table:
[pic]
Prescription table:
[pic]
Appointment table:
[pic]
Veterinary Doctor table:
[pic]
Drug Type table:
[pic]
Branch table:
[pic]
Normalization:
Normalization is the process of identifying the logical association between data-items and designed a database which will represent such associations but without suffering from file maintenance anomalies. Stages of normalization.

Normalization carried out in the following steps:
♣ Collect the data – the set of data items.
♣...
tracking img