The Entity Relationship Diagram

Design the database system for Petcare. Petcare is a medium sized veterinary surgery with six branches across London. They want a database system developed to handle the records of the animals they care for, prescriptions and appointments.

Entity-relationship diagram

An entity-relationship diagram (ERD) 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.

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.

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

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 Veterinary Surgery are identified as followed:

“Owner” is represented owners of animals which included name, address, home telephone number and mobile telephone number;

“Animal” is represented information of an animal which included sex, age and owner

“Breed” is represented information of breed for each animal type

“AnimalType” is represented type of animal (dog, cat, rabbit, etc)

“Appointment” 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

“Prescription” is represented record shows the period, in days, that the drug must be taken for. The cost of the medication is also recorded

“Drug” contains the name of the drug

“DrugType” contains the name of the drug type (Anti-biotic, Painkiller, Behaviour modification, Ear medication, Skin medication)

“Vet” is represented the following information kept about them: name, address, home telephone number, and mobile telephone number

“Branch” 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.

Identifying the relationships for the model:

Relationships mean that in a relational database, all entities have bonds between them, expressed as relationships. The relationship is a link between to entities, and it tells us something about which relationships exists between our entities. In the ER approach, more than one relationship can exist between any two entities.

What is one-to-many and many-to-many Entity Relationship~

One-to-Many Entity Relationship is by far the most common relationship type. It consists of either a one through many or a zero through many notations on one side of a relationship and a one and only one or zero or one notation on the other.

However, we can summarize all the entities to the Entity Relationship Data Model.

Diagram for Entity Relationship Data Model which describes the content and structure of the data held by Petcare.

Owner

Breed

Animal

Prescription

Drug

DrugType

AnimalType

Appointment

VetAnimalType

Vet

Branch

Fig 1 entity relationship diagram for the Petcare.

Normalized Tables

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, ie 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 can not be empty and can not 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 constraint that the data in the foreign keys must be consistent with the primary key of the table is linked. This is called reference integrity is to ensure that data entered is correct and is not unique

Definition of Normalization

Database normalization is a technique to reduce to the minimum duplication of information for designing relational database tables and in doing so to maintain the database for certain types of structural and logical problems, that is to say data anomalies. For example, when a given piece of information has multiple instances to be found in a table, this possible happen that these instances will not be kept consistent during the data in the table is updated, leading to a loss of data integrity. The such kind of problems are less vulnerable to a sufficiently normalized table, because its structure reflects the basic assumptions of the multiple instances of the same information should be used when a single instance of only.

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.

First normal form to second normal form:

To move form first normal form to second normal form I remove part-key dependencies. “A relation is in second normal form if and only if it is first normal form every non key attributes is fully functionally dependent on the primary key”

Take for instance the table named “Animal” here I have a tow compound key BreedID and AppointmentID. BreedID and AppointmentID have influence on the Animal. Hence I brake out the determinant and dependent data items into their own table.

Second normal form to third normal form:

To move form second normal forms to tired normal form I remove inter-data dependences (non-key dependences).

“A relation is in third normal form if and only if it its in second normal form and every non-key attribute is non-transitively dependent on the primary key”

A use full mnemonic for remembering the relation for normalization is the distortion of the legal Oath presented below:

No repeating group

The data items depended upon the key.

The while key And nothing but the key

So help me code.

Boyce-Codd Normal Form (BCNF)

A relation is in Boyce-Codd normal form (BCNF) if and only if every one of its important functional dependencies is a candidate key. The definition of BCNF is handling certain situations which 3NF does not deal with. To distinguish the characteristics of a relation between 3NF and BCNF are given below. Since it is such a relationship is unlikely to have these properties, in the real life design the most case of the relations in 3NF are also in BCNF. Therefore many authors propose a vaguely distinction between 3NF and BCNF when it involves about giving advice to normalize a design for long term. As the relations in 3NF and BCNF are slightly difference, it is a bit difficult to bring up with significantly examples. To be strictly conforming to the definition of 3NF does not handle a relation that:

1. Has multiple candidate keys, which

2. Those candidate keys are composite, and

3. The candidate key overlap. For example, the candidate keys have at least one common attribute.

Here is all tables after normalization:

OwnerID

Name

Address

HomePhoneNumber

Mobile

Table 2.11 VetAnimalType

Using a Database Management System (DBMS) set-up all of the above normalised tables

A Database Management System (DBMS) is a set of computer software programs. It controls the creation, maintenance, and the use of the database with computer as a platform or of an organization and its end users. It allows the organizations to place control of organization-wide database development in the hands of database administrators and other specialists. A DBMS is a set of system software. It helps the use of integrated collection of data records and files known as databases. It allows the programs of different user application to access the same database easily.

A DBMS is a set of software programs that controls the organization, storage, management, and retrieval of data in a database. DBMSs are categorized according to their data structures or types. The DBMS accepts requests for data from an application program and instructs the operating system to transfer the appropriate data. The queries and responses must be submitted and received according to a format that conforms to one or more applicable protocols. When a DBMS is used, information systems can be changed much more easily as the organization’s information requirements change. New categories of data can be added to the database without disruption to the existing system.

A DBMS includes four main parts: data structure, modeling language, database query language, and transaction mechanisms:

Components of DBMS

Data Definition Subsystem helps user to create and maintain the data dictionary and define the structure of the files in a database.

DBMS Engine accepts logical request from the various other DBMS subsystems, converts them into physical equivalent, and actually accesses the database and data dictionary as they exist on a storage device.

Data Manipulation Subsystem helps user to add, change, and delete information in a database and query it for valuable information. Software tools within the data manipulation subsystem are most often the primary interface between user and the information contained in a database. It allows user to specify its logical information requirements.

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

Application Generation Subsystem contains facilities to help users to develop transactions-intensive applications. It usually requires that user perform a detailed series of tasks to process a transaction. It facilities easy-to-use data entry screens, programming languages, and interfaces.

Microsoft Access 2003 to setup normalized tables and designed test data.

Set-up and test all of the following queries using Structured Query Language (SQL).

Structured Query Language (SQL) is a computer language which is designed for managing data in a relational database management system. It lets users to access and manipulate the database. The following task is to display how the Structured Query Language helps users to managing data in the Academic Human resources department system. An SQL ‘query’ manifests itself as a series of commands or statements. Queries may include arithmetic calculations and can use query elements or procedures stored in the system. SQL databases are designed from using SQL queries

The SQL language is used to ask database questions for the data stored inside the database: “what is the value of the element stored in “Name” index?”, or “what is the relationship between the data “name” and “salary”?”. Manipulative commands, like “insert data” or “update record” are also issued using SQL queries. A Data Definition Language allows SQL queries to create or administer the data structures, the ‘tables’ of data, which constitute the database. The actual syntax used is a little more strict and regimented than these natural language phrases suggest, but in effect this is the type of communication that will take place between a relational database and the person or device querying it using 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.

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;

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, 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

Task 5 assumptions and Improvements

Assumption

For the design the Petcare database system, we have made some assumptions before the implementation of the database. The entity relationship diagramming is created to identify each entity, relationship and attributes. 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.

Base on using the Entity relationship data model that describes the content and structure of the data help by the department. At the real environment, we can consider the database on three levels of abstraction: external, conceptual, and internal.

The external level also has the users’ views of the database. This is depending on their needs, different users’ access different parts of the database.

Improvements

Object-orientation and databases

Both object-oriented programming and relational database management systems (RDBMSs) are extremely common in software today. Since relational databases do not store objects directly (though some RDBMSs have object-oriented features to approximate this), there is a general need to bridge the two worlds.

The core of object-relational thinking is the ability to incorporate greater levels of abstraction into data models. This idea represents a major shift in the way that data modeling is done. Current relational databases are usually highly normalized but with little abstraction. Each “thing of interest” is instantiated as a relational table. As a result, systems frequently require numerous database tables and an equal number of screen modules and reports. The program modules are usually based directly on these tables with user workflow only instantiated through the way that the hundreds of screen modules interact. The object-oriented (OO) approach to data modeling will be something of a change for people familiar with entity relationship modeling. Even though we still end up with tables and relationships at the end of the process, the way to think about the modeling process has to change. Object-relational data models have several advantages over traditional data models: They require fewer entities (or “classes” in object-oriented terminology); They are more robust, in that they will support not only the specific user requirements gathered during the analysis phase, but will also usually support a broader class of requirements; They are more stable in that, as new requirements arise, the models will require fewer changes than traditional models

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.

Information is not valued unless it is legitimate. It must be valid and true to be worthy of use in decision-making. So, it is critical that all aspects of our system provide quality. To quote statistics based on erroneous data is considered foolish or criminal. The core of object-relational thinking is the ability to incorporate greater levels of abstraction into data models. This idea represents a major shift in the way that data modeling is done. Current relational databases are usually highly normalized but with little abstraction.

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

For the DBMS toolkit to review the database, the interface is essential function of most ICT systems it to interact with users. The three aspects are the content, control and format. The interface can be seen as collection of dialogues between the user and the ICT systems.