An entity-relationship diagram is a information mold technique that creates a graphical representation of the entities, and the relationships between entities, within an information system. This diagram is frequently used as a manner to visualise a relational database: each entity represents a database tabular array, and the relationship lines represent the keys in one tabular array that point to specific records in related tabular arraies. ERDs may besides be more abstract, non needfully capturing every tabular array needed within a database, but functioning to plot the major constructs and relationships.
An initial analysis of the Petcare has shown the following to assist us to develop the ERD and the database system:
Petcare keeps records of the animate beings they treat. Animals are defined by type ( Canis familiaris, cat, coney, etc ) and besides by peculiar strain. The other information kept about an animate being is: sex and age. All animate beings have an proprietor.
Breeds of Canis familiaris are: Terrier, Poodle, Beagle, Doberman, Wolfhound, Retriever, and Other Dog.
Breeds of cat are: Iranian, Thai, Tabby, Egyptian, Manx, and Other Cat.
Breeds of coney are: Dwarf, English Spot, Dutch, Satin and Other Rabbit.
The information kept on proprietors of animate beings is: name, reference, place telephone figure and nomadic telephone figure.
For assignments Petcare maintain the undermentioned information: the animate being the assignment is for, the proprietor of the animate being who requested the assignment, which veterinary physician the assignment is with, the clip and day of the month of the assignment, the diagnosing made and the charge made for the assignment.
Appointments are at peculiar subdivisions of Petcare.
The result of the assignment 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 alteration, Ear medicine, Skin medicine ) . The prescription record besides shows the period, in yearss, that the drug must be taken for. The cost of the medicine is besides recorded.
Each veterinary physician should hold the undermentioned information kept about them: name, reference, place telephone figure, and nomadic telephone figure. Each veterinary physician works at one subdivision of Petcare merely and a record should be kept of this. A veterinary physician may specialize in the intervention of one or more type of animate being ( Canis familiariss, cats, coneies ) .
A record is kept for each subdivision of Petcare. This records the name of the subdivision ( Enfield, Islington, Hackney, Holloway, Chingford or Leyton ) , reference of the subdivision, telephone figure, opening hours and exigency contact telephone figure.
Definition of relationship
A relationship is some association between entities. Relationship is shown by line between entities. Relationship lines indicate that each case of an entity may hold a relationship with cases of the affiliated entity, or frailty versa.
Definition of entity property
An entity is characterized by a figure of belongingss or properties. Valuess assigned to properties are used to separate one entity from another.
Definition of Optionality and Cardinality
Symbols at the terminals of the relationship lines indicate the optionality and the cardinality of each relationship. “ Optionality ” expresses whether the relationship is optional or compulsory. “ Cardinality ” expresses the maximal figure of relationships.
As a relationship line is followed from an entity to another, near the related entity two symbols will look. The first of those is the optionality index. A circle ( A™ ) indicates that the relationship is optional-the minimal figure of relationships between each case of the first entity and cases of the related entity is zero. One can believe of the circle as a nothing, or a missive O for “ optional. ” A shot ( | ) indicates that the relationship is mandatory-the minimal figure of relationships between each case of the first entity and cases of the related entity is one.
The 2nd symbol indicates cardinality. A shot ( | ) indicates that the maximal figure of relationships is one. A “ crows-foot ” ( ) indicates that many such relationships between cases of the related entities might be.
The entities for the Petcare
Before analysing the entities, allow hold a expression the definition of entity.
An entity is characterized by a figure of belongingss or properties. Valuess assigned to properties are used to separate one entity from another. An entity can specify as a thing which an organisation recognizes as being capable of an independent being and which can be unambiguously identified. In add-on, an entity is an abstraction from the complexnesss of some sphere. Each entity is shown in box within the ERD.
In this instance survey, the entities of the Petcare are identified as followed:
Owner
A.
“ Owner ” is represented the proprietors of animate beings. All animate beings have an proprietor. The information kept on proprietors of animate beings is: name, reference, place telephone figure and nomadic telephone figure.
Animal
B.
“ Animal ” is represented the animate beings they treat and attention. Animals are defined by type ( Canis familiaris, cat, coney, etc ) and besides by peculiar strain. The other information kept about an animate being is: sex and age. All animate beings have an proprietor.
Appointment
C.
“ Appointment ” is represented intervention takes at peculiar subdivisions of Pet Care. The animate being the assignment is for, the proprietor of the animate being who requested the assignment, which veterinary physician the assignment is with, the clip and day of the month of the assignment, the diagnosing made and the charge made for the assignment.
Veterinarian
D.
“ Veterinarian ” is represented the veterinary physician who takes intervention for the animate beings and go to the assignment. name, reference, place telephone figure, and nomadic telephone figure. Each veterinary physician works at one subdivision of Petcare merely and a record should be kept of this. A veterinary physician may specialize in the intervention of one or more type of animate being ( Canis familiariss, cats, coneies ) .
Breed
Tocopherol.
“ Breed ” is represented different animate beings. Breeds of Canis familiaris are: Terrier, Poodle, Beagle, Doberman, Wolfhound, Retriever, and Other Dog. Breeds of cat are: Iranian, Thai, Tabby, Egyptian, Manx, and Other Cat. Breeds of coney are: Dwarf, English Spot, Dutch, Satin and Other Rabbit.
ANIMAL TYPE
F.
“ Animal Type ” is represented types of animate beings. Dog, Cat and Rabbit
Branch
G.
“ Branch ” is represented the record is kept for each subdivision of Petcare. This records the name of the subdivision ( Enfield, Islington, Hackney, Holloway, Chingford or Leyton ) , reference of the subdivision, telephone figure, opening hours and exigency contact telephone figure.
Prescription
H.
“ Prescription ” is represented the result of the assignment. A prescription record contains the name of the drug type ( Anti-biotic, Painkiller, Behaviour alteration, Ear medicine, Skin medicine ) . The prescription record besides shows the period, in yearss, that the drug must be taken for. The cost of the medicine is besides recorded.
Drug
I.
“ Drug ” is represented the drug offered after the assignment and prescription made for the animate beings.
Drug TYPE
J.
“ Appointment ” is represented the prescription record contains the name of the drug type ( Anti-biotic, Painkiller, Behaviour alteration, Ear medicine, Skin medicine ) .
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: Meter ) Relationship
The relationship between an Owner and Animal entities can be said to be one-to-many ( 1: Meter ) if it can be defined in the undermentioned manner:
– The entity Owner holds many Animals
– The entity Animal clasp 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: Meter ) Relationship
The relationship between an Appointment and Animal entities can be said to be one-to-many ( 1: Meter ) if it can be defined in the undermentioned manner:
– 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: Meter ) Relationship
Veterinarian
The relationship between an Appointment and Vet entities can be MAY be one-to-many ( 1: Meter ) if it can be defined in the undermentioned manner:
– The entity Appointment Must be to one and merely one Vet
– The entity Vet May hold 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: Meter ) Relationship
The relationship between Breed and Animal entities can be said to be one-to-many ( 1: Meter ) if it can be defined in the undermentioned manner:
– The entity Breed has many Animals
– The entity Animal has merely 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: Meter ) 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: Meter ) if it can be defined in the undermentioned manner:
– The entity Breed has to merely one Animal Type
– The entity Animal Type has many sorts 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
Veterinarian
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 undermentioned manner:
– The entity Animal Type MAY affect one or many Veterinarians
– The entity Vet MAY specialise in the intervention of one or more type of animate beings.
The relationship between the entities Branch and Vet
Veterinarian
Branch
Figure 1.9 The Entities Prescription and Drug MAY be in one-to-many ( 1: Meter ) Relationship
Figure 1.7 The Entities Branch and Vet in one-to-many ( 1: Meter ) Relationship
The relationship between a Branch and Vet entities can be said to be one-to-many ( 1: Meter ) if it can be defined in the undermentioned manner:
– The entity Branch has may Vet
– The entity Vet works at one subdivision of Petcare merely.
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: Meter ) Relationship
The relationship between an Appointment and Prescription entities can be said MAY be one-to-many ( 1: Meter ) if it can be defined in the undermentioned manner:
– The entity Appointment MAY hold one or more Prescription
– The entity Prescription has to merely 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: Meter ) if it can be defined in the undermentioned manner:
– The entity Prescription MAY hold one or many Drugs.
– The entity Drug for merely 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: Meter ) Relationship
Figure 1.9 The Entities Prescription and Drug MAY in one-to-many ( 1: Meter ) Relationship
The relationship between an Drug and Drug Type entities can be said to be one-to-many ( 1: Meter ) if it can be defined in the undermentioned manner:
– 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
Veterinarian
Breed
Drug
Branch
Undertaking 2 – 20 Marks
Produce the ensuing normalised tabular arraies clearly bespeaking the primary and foreign keys.
Table is a information ( value ) , which is the theoretical account of the perpendicular columns ( which identifies the name ) and the horizontal lines. A specified figure of columns in the tabular array, but may be any figure of rows. Each row to place the subset of the values in the column, which has been identified as a campaigner key.
Table in another term relationship, although there is the difference that a tabular array is normally a multi-set ( bag ) as a series, and does non let transcripts. In add-on, the existent information rows, the panels are by and large associated with some other meta-data, such as limitations on the tabular array or the values in columns.
Primary key:
Primary key is a field or combination of Fieldss that unambiguously identify a record in the tabular array, so each ticket 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 alone to each record and the value will ne’er be duplicated in the same tabular array. A restraint is a regulation that defines what informations are valid for the country. So the primary cardinal restraint is the regulation which says that the primary cardinal field can non be empty and can non incorporate duplicate informations.
Database systems normally have more than one tabular array, and these are normally related in any manner. For illustration, a client tabular array and an Order tabular array relate to each other on a alone client figure. The client tabular array will ever be a record for each client, and the Order tabular array has a record for each order that the client has.
Foreign keys:
A foreign key ( sometimes referred to as the mention key ) is a key used to associate two tabular arraies together. Typically, you will hold a primary cardinal field from one tabular array and paste it into another tabular array, which becomes the foreign key ( the primary key in the original tabular array ) .
A foreign cardinal restraints that the informations in the foreign keys must be consistent with the primary key of the tabular array are linked. This is called mention unity is to guarantee that informations entered is right and is non alone
Definition of Standardization
Standardization is a set of regulations, which can be used to modify the manner informations is stored in tabular arraies. Normalization the procedure of change overing complex information constructions into sample, stable information constructions.
There are regulations for UNF, 1NF, 2NF, 3NF, BCNF, 4NF, 5NF and domain-Key NF. Most textbooks reference 5NF and DKNF merely in go throughing and note that they are non peculiarly applicable to be design procedure. Standardization is truly about the “ formalism of simple thoughts ” . All excessively frequently, the simpleness is lost in esoteric nomenclature and documents are “ frequently overly concerned with the formalism and supply really practical penetration ” .
In this undertaking, why need a standardization the database, it is because standardization is about planing a “ good ” database i.e. a set of related narratives with a lower limit of excess informations and no update, delete or insert anomalousnesss.
Standardization is a “ underside up ” attack to database design, The interior decorator interviews users and collects paperss – studies etc. The information on a study can be listed and so normalized to bring forth the needed tabular arraies and properties.
Animals Table
Animal Type Table
*Animal Type
Breeds of Dog
Breed
Breeds of Cat
Sexual activity
Breeds of Rabbit
Age
Other
Owner
Breeds of Rabbit Table
Breeds of Dog Table
Dwarf
Terrier
English
Poodle
Topographic point
Beagle
Dutch
Doberman
Satin
Wolfhound
Other Rabbit
Retriever
Other Dog
Appointments Table
Animal
Breeds of Cat Table
*Owner
Iranian
*Veterinary physician
Thai
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 yearss
Home Telephone
Cost of medicine
Mobile Telephone
Animals
Name of subdivision
Specialise in intervention of animate being types
Name of subdivision Table
*Appointments
*Branch
Enfield
Islington
Branch Table
Hackney
Name of Branch
Holloway
Address of subdivision
Chingford
Telephone figure
Leyton
Opening hours
Emergency contact telephone figure
Appointments
Drug Type Table
*Veterinary Doctor
Anti-biotic
Analgesic
Owner Table
Behaviour alteration
Owner Name
Ear medicine
Owner Address
Skin medicine
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 physician
In Veterinary physician Table
Primary key
Name of physician
Foreign keys:
Appointments
In Branch Table
Primary key
Name of subdivision
Foreign keys:
Veterinary physician
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
Undertaking 3 – 20 Marks
Using a Database Management System ( DBMS ) of your pick, set-up all of the above normalised tabular arraies, and dwell them with well-designed trial informations ( minimal 5 records per tabular array ) . Provide printouts of all tabular arraies.
Reasonable premises may be made with respect to informations.
The Definition of DBMS
An organized set of installations for accessing and keeping one or more databases
A shell which surrounds a database or series of databases and through which all interactions take topographic point with the database
A piece of package that manages all interactions with the database
The belongingss of database include Data Sharing, Data Integration, Data Integrity, Data Security, Data Abstraction and Data Independence
Create Table ‘s statement
Data definition involves installations for keeping scheme and tabular arraies. To organize a tabular array utilizing SQL the user needs to stipulate four constituents:
Name of the tabular array
Name of each of the columns in the tabular array
Data type of each column
Maximal length of each column
These four points are formulated together in a CREATE TABLE bid holding the undermentioned basic format:
CREATE TABLE & lt ; table name & gt ;
( & lt ; column name & gt ; & lt ; informations type & gt ; ( & lt ; length & gt ; ) ,
& lt ; column name & gt ; & lt ; informations type & gt ; ( & lt ; length & gt ; ) ,
The definition of Data Types
Data types act in portion as a definition for spheres ; they define certain belongingss refering the allowable values for a column. Every information value within a column must be of the same type. The SQL criterion defines some 15 informations types organized into the undermentioned groups:
Stringing Types: CHARACTER ( N ) , CHARACTER VARYING ( N ) , BIT ( N ) , BIT VARYING ( N )
Numeral 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 tabular array can be specified as being NOT NULL. This means that the user is so unable to come in void values into that column. The default specification for a column is void. That is, void values are allowed in a column. Any column can besides be defined as being UNIQUE. This clause prohibits the user from come ining extra values into a column. The combination of NOT NULL and UNIQE can be used to specify the features of a primary key.
The definition of Entity Integrity
Entity unity concerns primary keys. Entity unity is an unity regulation which states that every tabular array must hold a primary key and that the column or columns chosen to be the primary key should be alone and non void.
The definition of Referential Integrity
Referential unity concerns foreign keys. The referential unity regulation provinces that any foreign cardinal value can merely be in one of two provinces. The usual province of personal businesss is that the foreign cardinal value refers to a primary cardinal value of some tabular array in the database. Occasionally, and this will depend on the regulations of the concern, foreign cardinal value can be void. In this instance we are explicitly stating that either there is no relationship between the objects represented in the database or that this relationship unknown.
Five constituents of the DBMS
DBMS Engine accepts logical petition from the assorted other DBMS subsystems, converts them into physical equivalent, and in fact to the database and informations lexicons as they appear on a individual device.
Data definition subsystem helps users to make and the informations lexicon and the construction of the files in a database.
Data use subsystem helps users add, modify and delete information in a database question and for valuable information. Software tools within the informations managing subsystem is normally the primary interface between users and the information contained in a database. It allows user to the logical demands.
Application figure coevals subsystem includes installations, the user minutess to develop applications. It normally requires that users with a elaborate series of undertakings to a dealing. IT installations easy to utilize input masks, programming linguistic communications and interfaces.
Data Administration Subsystem helps users to pull off the database environment by supplying installations for backup and recovery, security direction, question optimisation, concurrence control and alteration direction.
Microsoft Office Access, once known as Microsoft Access is a relational database direction system from Microsoft, the relational Microsoft Jet Database Engine with a graphical user interface and package development tools.
Access shops informations in native format on the footing of the Access Jet database engine. It may besides, or a direct nexus to informations in other Access databases, Excel, SharePoint lists, text, XML, Outlook, HTML, dBase, Paradox, Lotus 1-2-3, or any ODBC-compliant informations container including Microsoft SQL Server, Oracle, MySQL and PostgreSQL. Software developers and informations designers can utilize it to develop package and non-programmer “ power users ” can utilize it for simple applications. It supports some object-oriented techniques, but behind a to the full object-oriented development tool.
There are two ways to see the database, whether you are a new one gap or something at that place. The information and the design position, it is easy to entree and get down it. You can implement subject when come ining informations through the informations entry signifiers. All sorts of regulations to guarantee that you have the right sort of informations can be carried out.
Access has become an industry criterion in desktop and database engine is really powerful. There are a big figure of templets, including the one you can download online, what the creative activity of new databases really easily. The ability, they can non merely be productive rapidly, but you can besides utilize things that meet your specific demands. Connectivity options are an advantage Entree databases can link to Excel spreadsheets, ODBC Connections, SQL Server and SharePoint Services sites for the unrecorded information. Tables in these beginnings can be linked and for the readying of studies.
I will utilize Microsoft Access 2003 to setup four normalized tabular arraies and designed trial informations. Microsoft Access 2003 is a relational database direction system from Microsoft for making computing machine databases. It provides an environment used to bring forth databases that can be accessed from media such as workstations and web.
The printouts are shown as follow
Normalized tabular arraies
Table: Animal
AnimalID
AnimalName
AnimalSex
AnimalAge
OwnerID
BreedID
1
Bobo
Meter
2
1
1
2
Little finger
F
3
2
4
3
Lucky
Meter
1
3
10
4
Zoe
F
2
4
8
5
Jack
Meter
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
Iranian
4
9
Thai
4
10
Tabby
4
11
Egyptian
4
12
Manx
4
13
Other Cat
4
14
Dwarf
5
15
English Topographic point
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
Analgesic
9
Behaviour alteration
10
Ear medicine
11
Skin medicine
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
Time period
CostOfMedication
DrugMustTake
AppointmentID
1
1 Time per twenty-four hours
200
True
3
2
3 Timess per twenty-four hours
500
True
4
3
2 Timess per twenty-four hours
300
True
5
4
4 Timess per twenty-four hours
250
True
6
5
2 Timess per twenty-four hours
250
True
7
Table: Veterinarian
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
Undertaking 4 – 20 Marks
Set-up and test all of the undermentioned questions utilizing Structured Query Language ( SQL ) . Provide printouts of SQL codification for each question and the end product produced when you run the question in the database you have developed:
Display the names and references of the subdivisions of Petcare and the names of all the veterinary physicians working at each of the subdivisions. Any specialism ( s ) of the veterinary physicians should besides be shown.
Display all the assignments for the whole of the Petcare administration. This should be ordered by day of the month. The consequence should expose the subdivision the assignment is at, the name of the veterinary physician the assignment is with, the day of the month and clip of the assignment, the name of the animate being the assignment is for, the type of animate being and the strain of the animate being.
Below is the SQL codification for create the tabular array.
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 codification for create the tabular array.
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
Little finger
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
Iranian
Leyton
Dr David Li
11/05/2010
12:00
Jack
Rabbit
Satin
Undertaking 5 – 10 Markss
Explain any premises you have made when analysing, planing and implementing the above database, warrant the attack you have taken and explicate any alternate attacks you could hold taken to any of the above undertakings. Discuss any alterations you would do to better your work.
Premise
We assume that the all animate beings records would be stored in the Appointments table even they have left Pet Care. We besides assume that the charges will non alter or increase. If the charges alterations harmonizing to clip, there may be different charges.
The premises are the follows:
We must necessitate to delegate vet to any new assignment.
Since each physician merely can work at one Branch at a clip and they can specialise more than one type of animate being. This mean all subdivision can for any type of animate being to do assignment.
set all the primary keys and foreign keys in all of the tabular arraies, each tabular array have their alone ID. Each tabular array should hold a primary key, and each tabular array can hold merely one primary key.
In the undertaking 3, we are utilizing DBMS, Microsoft Access 2003, to setup normalized tabular arraies and designed trial informations.
In the undertaking 4, we are utilizing SQL statements base on Microsoft Access 2003 to question our database to look into the database system can be fulfills the demands or non.
Improvement
Data is non “ information ” unless it is valued. Information value provides “ net income or addition ” merely when accessible or used. Accessibility and usage, through organized systems, provides “ competitory advantage ” . Speed determines the grade of competitory advantage. Computerized database systems are therefore, the ultimate method of high-velocity information retrieval. It is non hard to construct an organized database system. The “ trouble ” lies in the arduous, everyday undertaking of roll uping, categorising and keeping the monolithic sums of informations.
There are a several database plan applications for development and SQL database is really powerful tool. We can make the tabular arraies and question under SQL to setup the relationships of tabular arraies for bring forthing the analysis studies. The database security issue is really of import to protect the information and guarantee that the database systems are unafraid from unauthorised entree. Database security is usually assured by sing the informations control mechanisms available under a peculiar DBMS. Data control comes in two parts: forestalling unauthorised entree to informations, and forestalling unauthorised entree to the installations of peculiar DBMS. Database security will be a undertaking for the Database Administrator usually conducted in coaction with the organisation ‘s security expert. The public presentation is a relativistic construct. A volume analysis estimations of the maximal and mean figure of cases per entity. A usage analysis a precedences list of the most of import update and retrieval minutess expected to impact on the applications informations theoretical account. For the unity analysis built-in unity restraints and most of import sphere with extra restraints can be specified in an associated information lexicon.
The database systems have become so of import to organisations that the activity is devoted to be aftering for, monitoring and administrating the systems. We can concentrate on the planning and managerial activities relevant to database. It is defined the construct of informations disposal, the range of the informations disposal map, relate the costs and benefits of holding a information disposal maps. It besides defines the construct of a information lexicon and considers the issue of database security. The informations control is primary map for the database decision maker ( DBA ) . The DBA needs to be able to make three chief things:
– Prevent manque users from logging-on to the database
– Allocate entree to specific parts of the database to specific users
– Allocate entree 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
Entire words count: 4941
