Entity Relationship Data Model 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.

An entity-relationship diagram is a data modeling technique that creates a graphical representation of the entities, and the relationships between entities, within an information system. This diagram is often used as a way to visualize a relational database: each entity represents a database table, and the relationship lines represent the keys in one table that point to specific records in related tables. ERDs may also be more abstract, not necessarily capturing every table needed within a database, but serving to diagram the major concepts and relationships.

An initial analysis of the Petcare has shown the following to help us to develop the ERD and the database system:

Petcare keeps records of the animals they treat. Animals are defined by type (dog, cat, rabbit, etc) and also by particular breed. The other information kept about an animal is: sex and age. All animals have an owner.

Breeds of dog are: Terrier, Poodle, Beagle, Doberman, Wolfhound, Retriever, and Other Dog.

Breeds of cat are: Persian, Siamese, Tabby, Egyptian, Manx, and Other Cat.

Breeds of rabbit are: Dwarf, English Spot, Dutch, Satin and Other Rabbit.

The information kept on owners of animals is: name, address, home telephone number and mobile telephone number.

For appointments Petcare keep the following information: the animal the appointment is for, the owner of the animal who requested the appointment, which veterinary doctor the appointment is with, the time and date of the appointment, the diagnosis made and the charge made for the appointment.

Appointments are at particular branches of Petcare.

The outcome of the appointment might be a prescription with one or more drugs on it. A prescription record contains the name of the drug type (Anti-biotic, Painkiller, Behaviour modification, Ear medication, Skin medication). The prescription record also shows the period, in days, that the drug must be taken for. The cost of the medication is also recorded.

Each veterinary doctor should have the following information kept about them: name, address, home telephone number, and mobile telephone number. Each veterinary doctor works at one branch of Petcare only and a record should be kept of this. A veterinary doctor may specialise in the treatment of one or more type of animal (dogs, cats, rabbits).

A record is kept for each branch of Petcare. This records the name of the branch (Enfield, Islington, Hackney, Holloway, Chingford or Leyton), address of the branch, telephone number, opening hours and emergency contact telephone number.

Definition of relationship

A relationship is some association between entities. Relationship is shown by line between entities. Relationship lines indicate that each instance of an entity may have a relationship with instances of the connected entity, or vice versa.

Definition of entity attribute

An entity is characterized by a number of properties or attributes. Values assigned to attributes are used to distinguish one entity from another.

Definition of Optionality and Cardinality

Symbols at the ends of the relationship lines indicate the optionality and the cardinality of each relationship. "Optionality" expresses whether the relationship is optional or mandatory. "Cardinality" expresses the maximum number of relationships.

As a relationship line is followed from an entity to another, near the related entity two symbols will appear. The first of those is the optionality indicator. A circle ( ™ ) indicates that the relationship is optional-the minimum number of relationships between each instance of the first entity and instances of the related entity is zero. One can think of the circle as a zero, or a letter O for "optional." A stroke ( | ) indicates that the relationship is mandatory-the minimum number of relationships between each instance of the first entity and instances of the related entity is one.

The second symbol indicates cardinality. A stroke ( | ) indicates that the maximum number of relationships is one. A "crows-foot" ( ) indicates that many such relationships between instances of the related entities might exist.

The entities for the Petcare

Before analyzing the entities, let have a look the definition of entity.

An entity is characterized by a number of properties or attributes. Values assigned to attributes are used to distinguish one entity from another. An entity can define as a thing which an organization recognizes as being capable of an independent existence and which can be uniquely identified. In addition, an entity is an abstraction from the complexities of some domain. Each entity is shown in box within the ERD.

In this case study, the entities of the Petcare are identified as followed:

OWNER

A.

"Owner" is represented the owners of animals. All animals have an owner. The information kept on owners of animals is: name, address, home telephone number and mobile telephone number.

ANIMAL

B.

"Animal" is represented the animals they treat and care. Animals are defined by type (dog, cat, rabbit, etc) and also by particular breed. The other information kept about an animal is: sex and age. All animals have an owner.

APPOINTMENT

C.

"Appointment" is represented treatment takes at particular branches of Pet Care. The animal the appointment is for, the owner of the animal who requested the appointment, which veterinary doctor the appointment is with, the time and date of the appointment, the diagnosis made and the charge made for the appointment.

VET

D.

"Vet" is represented the veterinary doctor who takes treatment for the animals and attend the appointment. name, address, home telephone number, and mobile telephone number. Each veterinary doctor works at one branch of Petcare only and a record should be kept of this. A veterinary doctor may specialise in the treatment of one or more type of animal (dogs, cats, rabbits).

BREED

E.

"Breed" is represented different animals. Breeds of dog are: Terrier, Poodle, Beagle, Doberman, Wolfhound, Retriever, and Other Dog. Breeds of cat are: Persian, Siamese, Tabby, Egyptian, Manx, and Other Cat. Breeds of rabbit are: Dwarf, English Spot, Dutch, Satin and Other Rabbit.

ANIMAL TYPE

F.

"Animal Type" is represented types of animals. Dog, Cat and Rabbit

BRANCH

G.

"Branch" is represented the record is kept for each branch of Petcare. This records the name of the branch (Enfield, Islington, Hackney, Holloway, Chingford or Leyton), address of the branch, telephone number, opening hours and emergency contact telephone number.

PRESCRIPTION

H.

"Prescription" is represented the outcome of the appointment. A prescription record contains the name of the drug type (Anti-biotic, Painkiller, Behaviour modification, Ear medication, Skin medication). The prescription record also shows the period, in days, that the drug must be taken for. The cost of the medication is also recorded.

DRUG

I.

"Drug" is represented the drug offered after the appointment and prescription made for the animals.

DRUG TYPE

J.

"Appointment" is represented the prescription record contains the name of the drug type (Anti-biotic, Painkiller, Behaviour modification, Ear medication, Skin medication).

The Entities Relationships

OWNERThe relationship between the entities Owner and Animal

ANIMAL

Figure 1.1 The Entities Owner and Animal in one-to-many (1: M) Relationship

The relationship between an Owner and Animal entities can be said to be one-to-many (1: M) if it can be defined in the following way:

- The entity Owner holds many Animals

- The entity Animal hold by one Owner

The relationship between the entities Animal and Appointment

ANIMAL

APPOINTMENT

Figure 1.2 The Entities Animal and Appointment MAY be in one-to-many (1: M) Relationship

The relationship between an Appointment and Animal entities can be said to be one-to-many (1: M) if it can be defined in the following way:

- The entity Animal MAY have Appointment

- The entity Appointment MUST for one Animal.

The relationship between the entities Appointment and Vet

APPOINTMENT

Figure 1.3 The Entities Appointment in and Vet MAY be one-to-many (1: M) Relationship

VET

The relationship between an Appointment and Vet entities can be MAY be one-to-many (1: M) if it can be defined in the following way:

- The entity Appointment Must be to one and only one Vet

- The entity Vet May have zero or many Appointments

The relationship between the entities Breed and Animal

ANIMAL

BREED

Figure 1.4 The Entities Breed and Animal in one-to-many (1: M) Relationship

The relationship between Breed and Animal entities can be said to be one-to-many (1: M) if it can be defined in the following way:

- The entity Breed has many Animals

- The entity Animal has only one type of Breed

The relationship between the entities Breed and Animal Type

ANIMAL TYPE

BREED

Figure 1.5 The Entities Breed and Animal Type in one-to-many (1: M) Relationship

Figure 1.6 The Entities Animal Type and Vet MAY in many-to-many (M : M) Relationship

The relationship between Breed and Animal Type entities can be said to be one-to-many (1: M) if it can be defined in the following way:

- The entity Breed has to only one Animal Type

- The entity Animal Type has many kinds of Breed

The relationship between the entities Appointment and Vet

Figure 1.6 The Entities Animal Type and Vet MAY in Many-to-many (M: M) Relationship

VET

ANIMAL TYPE

The relationship between Animal Type and Vet entities can be said MAY be many-to-many (M : M) if it can be defined in the following way:

- The entity Animal Type MAY involve one or many Vets

- The entity Vet MAY specialize in the treatment of one or more type of animals.

The relationship between the entities Branch and Vet

VET

BRANCH

Figure 1.9 The Entities Prescription and Drug MAY be in one-to-many (1: M) Relationship

Figure 1.7 The Entities Branch and Vet in one-to-many (1: M) Relationship

The relationship between a Branch and Vet entities can be said to be one-to-many (1: M) if it can be defined in the following way:

- The entity Branch has may Vets

- The entity Vet works at one branch of Petcare only.

The relationship between the entities Appointment and Prescription

APPOINTMENT

PRESCRIPTION

Figure 1.8 The Entities Appointment and Prescription MAY be in one-to-many (1: M) Relationship

The relationship between an Appointment and Prescription entities can be said MAY be one-to-many (1: M) if it can be defined in the following way:

- The entity Appointment MAY have one or more Prescription

- The entity Prescription has to only one Appointment

The relationship between the entities Prescription and Drug

PRESCRIPTION

DRUG

The relationship between an Prescription and Drug entities can be said MAY be one-to-many (1: M) if it can be defined in the following way:

- The entity Prescription MAY have one or many Drugs.

- The entity Drug for only Prescription

The relationship between the entities Drug and Drug Type

DRUG

DRUG TYPE

Figure 1.10 The Entities Drug and Drug Type in one-to-many (1: M) Relationship

Figure 1.9 The Entities Prescription and Drug MAY in one-to-many (1: M) Relationship

The relationship between an Drug and Drug Type entities can be said to be one-to-many (1: M) if it can be defined in the following way:

- The entity Drug has to be one Drug Type

Entity Relationship Data Model

- The entity Drug Type has many drugs.

ANIMAL

APPOINTMENT

ANIMAL TYPE

PRESCRIPTION

OWNER

DRUG TYPE

VET

BREED

DRUG

BRANCH

Task 2 - 20 Marks

Produce the resulting normalised tables clearly indicating the primary and foreign keys.

Table is a data (value), which is the model of the vertical columns (which identifies the name) and the horizontal lines. A specified number of columns in the table, but may be any number of rows. Each row to identify the subset of the values in the column, which has been identified as a candidate key.

Table in another term relationship, although there is the difference that a table is usually a multi-set (bag) as a series, and does not allow copies. In addition, the actual data rows, the panels are generally associated with some other meta-data, such as restrictions on the table or the values in columns.

Primary key:

Primary key is a field or combination of fields that uniquely identify a record in the table, so each tag can be placed without confusion.

Primary key is the field (s) (primary key can be made up of more than one field) that uniquely identifies each record, i.e. the primary key is unique to each record and the value will never be duplicated in the same table. A constraint is a rule that defines what data are valid for the area. So the primary key constraint is the rule which says that the primary key field cannot be empty and cannot contain duplicate data.

Database systems usually have more than one table, and these are usually related in any way. For example, a customer table and an Order table relate to each other on a unique customer number. The customer table will always be a record for each customer, and the Order table has a record for each order that the customer has.

Foreign keys:

A foreign key (sometimes referred to as the reference key) is a key used to link two tables together. Typically, you will have a primary key field from one table and paste it into another table, which becomes the foreign key (the primary key in the original table).

A foreign key constraints that the data in the foreign keys must be consistent with the primary key of the table are linked. This is called reference integrity is to ensure that data entered is correct and is not unique

Definition of Normalization

Normalization is a set of rules, which can be used to modify the way data is stored in tables. Normalization the process of converting complex data structures into sample, stable data structures.

There are rules for UNF, 1NF, 2NF, 3NF, BCNF, 4NF, 5NF and domain-Key NF. Most textbooks mention 5NF and DKNF only in passing and note that they are not particularly applicable to be design process. Normalization is really about the "formalism of simple ideas". All too often, the simplicity is lost in esoteric terminology and papers are "often excessively concerned with the formalism and provide very practical insight".

In this project, why need a normalization the database, it is because normalization is about designing a "good" database i.e. a set of related tales with a minimum of redundant data and no update, delete or insert anomalies.

Normalization is a "bottom up" approach to database design, The designer interviews users and collects documents - reports etc. The data on a report can be listed and then normalized to produce the required tables and attributes.

Animals Table

Animal Type Table

*Animal Type

Breeds of Dog

Breed

Breeds of Cat

Sex

Breeds of Rabbit

Age

Other

Owner

Breeds of Rabbit Table

Breeds of Dog Table

Dwarf

Terrier

English

Poodle

Spot

Beagle

Dutch

Doberman

Satin

Wolfhound

Other Rabbit

Retriever

Other Dog

Appointments Table

Animal

Breeds of Cat Table

*Owner

Persian

*Veterinary doctor

Siamese

Time

Tabby

Date

Egyptian

Diagnosis

Manx

Charge Made

Other Cat

Branch

Veterinary Doctor Table

Prescription Record Table

Name of Doctor

*Drug type

Address

Drug taken period in days

Home Telephone

Cost of medication

Mobile Telephone

Animals

Name of branch

Specialise in treatment of animal types

Name of branch Table

*Appointments

*Branch

Enfield

Islington

Branch Table

Hackney

Name of Branch

Holloway

Address of branch

Chingford

Telephone number

Leyton

Opening hours

Emergency contact telephone number

Appointments

Drug Type Table

*Veterinary Doctor

Anti-biotic

Painkiller

Owner Table

Behaviour modification

Owner Name

Ear medication

Owner Address

Skin medication

Home Telephone

*Prescription Record

Mobile Telephone

*Animals

In Animals Table

Primary key

Owner

Foreign keys:

Animal Type

Breed

In Appointments Table

Primary key

Animal

Foreign keys:

Owner

Veterinary doctor

In Veterinary doctor Table

Primary key

Name of doctor

Foreign keys:

Appointments

In Branch Table

Primary key

Name of branch

Foreign keys:

Veterinary doctor

In Prescription Record Table

Primary key

Animals

Foreign keys:

Animals

Drug Type

In Name of Branch Table

Primary key

Foreign keys:

Branch

In Drug Type Table

Primary key

Foreign keys:

Prescription Record

In Owner Table

Primary key

Owner Name

Foreign keys:

Animals

Task 3 - 20 Marks

Using a Database Management System (DBMS) of your choice, set-up all of the above normalised tables, and populate them with well-designed test data (minimum 5 records per table). Provide printouts of all tables.

Reasonable assumptions may be made with regard to data.

The Definition of DBMS

An organized set of facilities for accessing and maintaining one or more databases

A shell which surrounds a database or series of databases and through which all interactions take place with the database

A piece of software that manages all interactions with the database

The properties of database include Data Sharing, Data Integration, Data Integrity, Data Security, Data Abstraction and Data Independence

Create Table's statement

Data definition involves facilities for maintaining schemas and tables. To form a table using SQL the user needs to specify four components:

Name of the table

Name of each of the columns in the table

Data type of each column

Maximum length of each column

These four items are formulated together in a CREATE TABLE command having the following basic format:

CREATE TABLE <table name>

(<column name><data type>(<length>),

<column name><data type>(<length>),

The definition of Data Types

Data types act in part as a definition for domains; they define certain properties concerning the allowable values for a column. Every data value within a column must be of the same type. The SQL standard defines some fifteen data types organized into the following groups:

String Types: CHARACTER(N), CHARACTER VARYING(N), BIT(N), BIT VARYING(N)

Numeric Types: NUMERIC(M,N), DECIMAL(M,N), INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION

Datetime Types: DATE, TIME

The definition of Not Null and Unique

Any column in a table can be specified as being NOT NULL. This means that the user is then unable to enter null values into that column. The default specification for a column is null. That is, null values are allowed in a column. Any column can also be defined as being UNIQUE. This clause prohibits the user from entering duplicate values into a column. The combination of NOT NULL and UNIQE can be used to define the characteristics of a primary key.

The definition of Entity Integrity

Entity integrity concerns primary keys. Entity integrity is an integrity rule which states that every table must have a primary key and that the column or columns chosen to be the primary key should be unique and not null.

The definition of Referential Integrity

Referential integrity concerns foreign keys. The referential integrity rule states that any foreign key value can only be in one of two states. The usual state of affairs is that the foreign key value refers to a primary key value of some table in the database. Occasionally, and this will depend on the rules of the business, foreign key value can be null. In this case we are explicitly saying that either there is no relationship between the objects represented in the database or that this relationship unknown.

Five components of the DBMS

DBMS Engine accepts logical request from the various other DBMS subsystems, converts them into physical equivalent, and in fact to the database and data dictionary as they appear on a single device.

Data definition subsystem helps users to create and the data dictionary and the structure of the files in a database.

Data manipulation subsystem helps users add, modify and delete information in a database query and for valuable information. Software tools within the data handling subsystem is usually the primary interface between users and the information contained in a database. It allows user to the logical requirements.

Application number generation subsystem includes facilities, the user transactions to develop applications. It usually requires that users with a detailed series of tasks to a transaction. IT facilities easy to use input masks, programming languages and interfaces.

Data Administration Subsystem helps users to manage the database environment by providing facilities for backup and recovery, security management, query optimization, concurrency control and change management.

Microsoft Office Access, formerly known as Microsoft Access is a relational database management system from Microsoft, the relational Microsoft Jet Database Engine with a graphical user interface and software development tools.

Access stores data in native format on the basis of the Access Jet database engine. It may also, or a direct link to data in other Access databases, Excel, SharePoint lists, text, XML, Outlook, HTML, dBase, Paradox, Lotus 1-2-3, or any ODBC-compliant data container including Microsoft SQL Server, Oracle, MySQL and PostgreSQL. Software developers and data architects can use it to develop software and non-programmer "power users" can use it for simple applications. It supports some object-oriented techniques, but behind a fully object-oriented development tool.

There are two ways to view the database, whether you are a new one opening or something there. The data and the design view, it is easy to access and start it. You can enforce discipline when entering data through the data entry forms. All kinds of rules to ensure that you have the right kind of data can be carried out.

Access has become an industry standard in desktop and database engine is very powerful. There are a large number of templates, including the one you can download online, what the creation of new databases very easy. The ability, they cannot just be productive quickly, but you can also use things that meet your specific needs. Connectivity options are an advantage Access databases can connect to Excel spreadsheets, ODBC Connections, SQL Server and SharePoint Services sites for the live data. Tables in these sources can be linked and for the preparation of reports.

I will use Microsoft Access 2003 to setup four normalized tables and designed test data. Microsoft Access 2003 is a relational database management system from Microsoft for creating computer databases. It provides an environment used to generate databases that can be accessed from media such as workstations and web.

The printouts are shown as follow

Normalized tables

Table: Animal

AnimalID

AnimalName

AnimalSex

AnimalAge

OwnerID

BreedID

1

Bobo

M

2

1

1

2

Pinky

F

3

2

4

3

Lucky

M

1

3

10

4

Zoe

F

2

4

8

5

Jack

M

3

5

17

Table: AnimalType

AnimalTypeID

AnimalTypeName

VetAnimalTypeID

3

Dog

1

4

Cat

3

5

Rabbit

2

6

Bird

4

7

Pet Rats

5

Table: Appointment

AppointmentID

Date

Time

Diagnosis

Charge

AnimalID

VetID

3

01/05/2010

10:00

TRUE

900

1

2

4

02/05/2010

14:00

TRUE

800

2

3

5

06/05/2010

11:00

TRUE

900

3

4

6

10/05/2010

11:00

TRUE

1000

4

5

7

11/05/2010

12:00

TRUE

700

5

6

Table: Branch

BranchID

BranchName

Address

PhoneNumber

EmergencyPhoneNumber

OpeningHours

1

Enfield

1 First Street

852-21237894

852-24567896

10:00 - 17:00

2

Islington

2 Second Road

852-29638521

852-9638524

10:00 - 17:00

3

Hackney

3 Third Street

852-21596214

852-27418526

10:00 - 17:00

4

Holloway

44 First Road

852-24561235

852-24568523

10:00 - 17:00

6

Chingford

55 King Road

852-21563571

852-24561238

10:00 - 17:00

7

Leyton

99 Fast Street

852-24567892

852-25896325

10:00 - 17:00

Table: Breed

BreedID

BreedName

AnimalTypeID

1

Terrier

3

2

Poodle

3

3

Beagle

3

4

Doberman

3

5

Wolfhound

3

6

Retriever

3

7

Other Dog

3

8

Persian

4

9

Siamese

4

10

Tabby

4

11

Egyptian

4

12

Manx

4

13

Other Cat

4

14

Dwarf

5

15

English Spot

5

16

Dutch

5

17

Satin

5

18

Other Rabbit

5

Table: Drug

DrugID

DrugName

DrugTypeID

PrescriptionID

1

Ascorbic Acid

7

1

2

Biotin

8

2

3

Dantrolene

11

2

4

Cyhexatin

7

3

5

Endothall

10

1

6

Cefixime

10

4

7

Dextran

8

5

8

Captan

9

5

Table: DrugType

DrugTypeID

DrugTypeName

7

Anti-biotic

8

Painkiller

9

Behaviour modification

10

Ear medication

11

Skin medication

Table: Owner

OwnerID

Name

Address

HomePhoneNumber

Mobile

1

Ken Wong

12 King Street

852-27894561

852-94561237

2

Mary Lee

43 Queen Road

852-22586321

852-97895263

3

Cherry Lo

55 First Road

852-25689231

852-97845126

4

John Chan

99 Shell Street

852-28965785

852-92345789

5

Dick Cheng

88 Main Street

852-28880011

852-98819988

Table: Prescription

PrescriptionID

Period

CostOfMedication

DrugMustTake

AppointmentID

1

1 Time per day

200

TRUE

3

2

3 Times per day

500

TRUE

4

3

2 Times per day

300

TRUE

5

4

4 Times per day

250

TRUE

6

5

2 Times per day

250

TRUE

7

Table: Vet

VetID

VetName

Address

HomePhoneNumber

Mobile

BranchID

VetAnimalTypeID

2

Dr Mary Lee

9 First Street

852-24568523

852-96325874

1

1

3

Dr Peter Lok

8 Second Street

852-29632582

852-98547852

2

2

4

Dr John Ma

7 Main Street

852-21452369

852-95826393

1

3

5

Dr Ann Wong

5 High Street

852-26352698

852-92145896

4

4

6

Dr David Li

3 Happy Street

852-28526394

852-91258456

7

5

Table: VetAnimalType

VetAnimalTypeID

VetID

AnimalTypeID

1

2

3

2

3

5

3

4

4

4

5

6

5

6

7

Task 4 - 20 Marks

Set-up 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 addresses 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 organisation. 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 is the SQL code for create the table.

SELECT Branch.BranchName, Branch.Address, VET.VetName, AnimalType.AnimalTypeName

FROM (AnimalType INNER JOIN VetAnimalType ON AnimalType.AnimalTypeID = VetAnimalType.AnimalTypeID) INNER JOIN (Branch INNER JOIN VET ON Branch.BranchID = VET.BranchID) ON VetAnimalType.VetAnimalTypeID = VET.VetAnimalTypeID;

BranchName

Address

VetName

AnimalTypeName

Enfield

1 First Street

Dr Mary Lee

Dog

Islington

2 Second Road

Dr Peter Lok

Rabbit

Enfield

1 First Street

Dr John Ma

Cat

Holloway

44 First Road

Dr Ann Wong

Bird

Leyton

99 Fast Street

Dr David Li

Pet Rats

Below is the SQL code for create the table.

SELECT Branch.BranchName, Vet.VetName, Appointment.Date, Appointment.Time, Animal.AnimalName, AnimalType.AnimalTypeName, Breed.BreedName

FROM (Branch INNER JOIN Vet ON Branch.BranchID = Vet.BranchID) INNER JOIN ((AnimalType INNER JOIN Breed ON AnimalType.AnimalTypeID = Breed.AnimalTypeID) INNER JOIN (Animal INNER JOIN Appointment ON Animal.AnimalID = Appointment.AnimalID) ON Breed.BreedID = Animal.BreedID) ON Vet.VetID = Appointment.VetID

ORDER BY Appointment.Date;

BranchName

VetName

Date

Time

AnimalName

AnimalTypeName

BreedName

Enfield

Dr Mary Lee

01/05/2010

10:00

Bobo

Dog

Terrier

Islington

Dr Peter Lok

02/05/2010

14:00

Pinky

Dog

Doberman

Enfield

Dr John Ma

06/05/2010

11:00

Lucky

Cat

Tabby

Holloway

Dr Ann Wong

10/05/2010

11:00

Zoe

Cat

Persian

Leyton

Dr David Li

11/05/2010

12:00

Jack

Rabbit

Satin

Task 5 - 10 marks

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.

Assumption

We assume that the all animals records would be stored in the Appointments table even they have left Pet Care. We also assume that the charges will not change or increase. If the charges changes according to time, there may be different charges.

The assumptions are the follows:

We must need to assign vet to any new appointment.

Since each doctor only can work at one Branch at a time and they can specialize more than one type of animal. This mean all branch can for any type of animal to make appointment.

set all the primary keys and foreign keys in all of the tables, each table have their unique ID. Each table should have a primary key, and each table can have only one primary key.

In the task 3, we are using DBMS, Microsoft Access 2003, to setup normalized tables and designed test data.

In the task 4, we are using SQL statements base on Microsoft Access 2003 to query our database to investigate the database system can be fulfills the requirements or not.

Improvement

Data is not "information" unless it is valued. Information value provides "profit or gain" only when accessible or used. Accessibility and use, through organized systems, provides "competitive advantage". Speed determines the degree of competitive advantage. Computerized database systems are thus, the ultimate method of high-speed information retrieval. It is not difficult to build an organized database system. The "difficulty" lies in the laborious, mundane task of collecting, categorizing and maintaining the massive amounts of data.

There are a several database program applications for development and SQL database is very powerful tool. We can create the tables and query under SQL to setup the relationships of tables for generating the analysis reports. The database security issue is very important to protect the data and ensure that the database systems are secure from unauthorized access. Database security is normally assured by sing the data control mechanisms available under a particular DBMS. Data control comes in two parts: preventing unauthorized access to data, and preventing unauthorized access to the facilities of particular DBMS. Database security will be a task for the Database Administrator normally conducted in collaboration with the organization's security expert. The performance is a relativistic concept. A volume analysis estimates of the maximum and average number of instances per entity. A usage analysis a priorities list of the most important update and retrieval transactions expected to impact on the applications data model. For the integrity analysis inherent integrity constraints and most important domain with additional constraints can be specified in an associated data dictionary.

The database systems have become so important to organizations that the activity is devoted to planning for, monitoring and administering the systems. We can focus on the planning and managerial activities relevant to database. It is defined the concept of data administration, the scope of the data administration function, relate the costs and benefits of having a data administration functions. It also defines the concept of a data dictionary and considers the issue of database security. The data control is primary function for the database administrator (DBA). The DBA needs to be able to do three main things:

- Prevent would-be users from logging-on to the database

- Allocate access to specific parts of the database to specific users

- Allocate access to specific operations to specific users

Referencing and Bibliographies

- Date, C. J. (1999), An Introduction to Database Systems (8th ed.). Addison-Wesley Longman.

- H.-J. Schek, P. Pistor Data Structures for an Integrated Data Base Management and Information Retrieval System

- Development of an object-oriented DBMS; Portland, Oregon, United States; Pages: 472 - 482;

www.oracle.com

www.everbesthk.com

www.mysql.com

www.intel.com

www.microsoft.com

www.edb.gov.hk

Total words count: 4941

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.