The Entity Relationship Diagram

The Entity Relationship DiagramDesign the entropybase dodge for Petcargon. Petc ar is a medium sized veterinary surgery with six branches across London. They insufficiency a selective randomnessbase system developed to handle the records of the physicals they c ar for, prescriptions and participations.Entity- traffichip plotAn entity- alliance diagram (ERD) is a info fabricing technique that readys a graphical theatrical of the entities, and the relationships among entities, within an in inningation system. This diagram is often apply as a way to visualize a relational selective informationbase distri entirelyively entity represents a entropybase parry, and the relationship lines represent the discloses in virtuoso disconcert that point to limited records in related tables. ERDs may too be to a greater extent abstract, not necessarily capturing both table needed within a informationbase, scarce serving to diagram the major concepts and relationships.Definitio n of relationshipA relationship is nigh association betwixt entities. Relationship is sh experience by line mingled with entities. Relationship lines indicate that distributively instance of an entity may harbour a relationship with instances of the connected entity, or vice versa.Definition of entity deputeAn entity is characterized by a number of properties or attributes. Values assigned to attributes argon utilised to distinguish wholeness entity from another.Definition of Optionality and CardinalitySymbols at the dyings of the relationship lines indicate the optionality and the cardinality of separately relationship. Optionality expresses whether the relationship is optional or mandatory. Cardinality expresses the maximum number of relationships.Before analyzing the entities, permit withstand a look the definition of entity.An entity support define as a thing which an organization recognizes as being capable of an self-directed existence and which quarter be inco mparablely 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 followedOwner is delineate owners of animals which include bid, address, inhabitation teleph unmatchable number and mobile telephone number animal is represented in ashesation of an animal which included sex, age and owner do is represented information of form for each animal typeAnimalType is represented type of animal (dog, cat, rabbit, etc)Appointment Petcare keep the following information the animal the assigning is for, the owner of the animal who requested the appointment, which veterinary doctor the appointment is with, the time and visualize of the appointment, the diagnosis do and the charge made for the appointmentPrescription is represented record shows the period, in days, that the drug must be taken for. The cost of the medication is also record edDrug contains the name of the drugDrugType contains the name of the drug type (Anti-biotic, Painkiller, Behaviour modification, Ear medication, come up medication) old stager is represented the following information kept about them name, address, home telephone number, and mobile telephone numberBranch is kept for each branch of Petcare. This records the name of the branch (En field of honor, Islington, Hackney, Holloway, Chingford or Leyton), address of the branch, telephone number, opening do hours and emergency contact telephone number.Identifying the relationships for the modelRelationships mean that in a relational database, all entities bring forth bonds among them, expressed as relationships. The relationship is a wed amid to entities, and it tells us something about which relationships exists between our entities. In the ER approach, more(prenominal) than one relationship closedown exist between either two entities.What is one-to- many an(prenominal) and many-to -many Entity RelationshipOne-to-Many Entity Relationship is by far the well-nigh 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 just now one or zero or one notation on the other.However, we can summarize all the entities to the Entity Relationship selective information Model.Diagram for Entity Relationship Data Model which describes the content and structure of the data held by Petcare.Owner formAnimalPrescriptionDrugDrugTypeAnimalTypeAppointmentVetAnimalTypeVetBranchnumber 1 entity relationship diagram for the Petcare.Normalized disconcerts card 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 grade the subset of the values in the column, which has been identified as a candidate divulge.Table in another term relationship, althoug h thither 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(prenominal) as restrictions on the table or the values in columns. patriarchal cayPrimary depict is a field or conclave of fields that drollly identify a record in the table, so each tag can be placed without confusion.Primary key is the field (s) ( master(a) key can be made up of more than one field) that uniquely identifies each record, ie the first key is unique to each record and the value will never be duplicated in the same table. A bashfulness is a rule that defines what data are valid for the area. So the master(a) key constraint is the rule which says that the uncreated key field can not be empty and can not contain duplicate data.Database systems usually take a shit more than one table, and these are usually related in any way. For example, a customer t able and an Order table relate to each other on a unique customer number. The customer table will ever be a record for each customer, and the Order table has a record for each order that the customer has.Foreign keysA foreign key (sometimes referred to as the reference key) is a key use of goods and servicesd 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 fall off and is not uniqueDefinition of NormalizationDatabase universalization 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 luculent problems, that is to sa y data anomalies. For example, when a given piece of information has multiple instances to be rig 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 penetrable 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 unfeignedly about the formalism of simple ideas. All too often, the simplicity is preoccupied in esoteric terminology and papers are often also concerned with the formalism and provide very practical insight.In this project, why need a normalization the database, it is because normaliz ation is about designing a good database i.e. a set of related tales with a minimum of bare(a) data and no update, delete or insert anomalies.Normalization is a bottom up approach to database design, The designer interviews drug users and collects documents reports etc. The data on a report can be listed and then normalized to produce the needed tables and attributes.First normal form to second normal formTo go on 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 totaly functionally dependent on the primary key harbor for instance the table named Animal here I have a tow compound key BreedID and AppointmentID. BreedID and AppointmentID have influence on the Animal. because I brake out the determinant and dependent data items into their own table.Second normal form to third normal formTo move form second normal forms to tired normal form I remove in ter-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 keyA use full mnemonic for remembering the relation for normalization is the distortion of the legal adjuration presented belowNo repeating groupThe data items depended upon the key.The while key And nothing but the keySo jock me cypher.Boyce-Codd Normal course of action (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 manipulation 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 see 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 that1. Has multiple candidate keys, which2. Those candidate keys are composite, and3. The candidate key overlap. For example, the candidate keys have at least one common attribute. present is all tables after normalizationOwnerIDNameAddressHomePhoneNumberMobileTable 2.11 VetAnimalTypeUsing a Database Management System (database circumspection system) set-up all of the above normalised tablesA Database Management System (database counselling system) is a set of ready reckoner software plans. It examines 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 d evelopment in the hands of database executive directors and other specialists. A DBMS is a set of system software. It helps the use of integrated parade of data records and files known as databases. It allows the programs of diametrical user application to door the same database easily.A DBMS is a set of software programs that controls the organization, storage, management, and recovery 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 organizations information requirements change. New categories of data can be added to the database without disruption to the actual system.A DBMS includes four main parts data structure, casting language, database ask language, and transaction mechanismsComponents of DBMSData 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 head it for valuable information. Software tools within the data manipulation subsystem are most often the primary embrasure 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 manag ement.Application Generation Subsystem contains facilities to help users to develop transactions-intensive applications. It usually requires that user perform a detailed series of designates to process a transaction. It facilities easy-to-use data entry screens, programming languages, and interfaces.Microsoft Access 2003 to frame-up normalized tables and designed test data.Set-up and test all of the following queries using incorporate interrogative 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 ostentation 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 datab ases are designed from using SQL queriesThe 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 impression 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 bring out the names and addresses of the branches of Petcare and the names of all the veterinary doctors workings at each of th e branches. Any specialism(s) of the veterinary doctors should also be shown. under is the SQL code for create the table.SELECT Branch.BranchName, Branch.Address, VET.VetName, AnimalType.AnimalTypeNameFROM (AnimalType interior(a) JOIN VetAnimalType ON AnimalType.AnimalTypeID = VetAnimalType.AnimalTypeID) INNER JOIN (Branch INNER JOIN VET ON Branch.BranchID = VET.BranchID) ON VetAnimalType.VetAnimalTypeID = VET.VetAnimalTypeIDDisplay 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.BreedNameFROM (Branch INNER JOIN Vet ON Br anch.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.VetIDTask 5 assumptions and ImprovementsAssumptionFor 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 followsWe must need to assign vet to any new appointment.Since each doctor only can work at one Branch at a time and they can secernate 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 u sing 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.ImprovementsObject-orientation and databasesBoth 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 thought is the ability to incorporate greater levels of abstraction into data models. This idea represents a major shift in the way that data simulate is done. incumbent 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 establish directly on these tables with user work flow 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 traditionalistic 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 permanent in that, as new requirements arise, the models will require fewer changes than traditional modelsData is not information unless it is cute. Information value provides profit or gain only when accessible or used. Accessibility and use, through organised systems, provides competitive advantage. Speed determines the degree of competitive advantage. Computerized database systems are thus, the ultimate order 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 w orthy 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 cerebration 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. rate of flow 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, monitor and administering the systems. We can focus on the planning and managerial activities relevant to database. It is outlined the concept of data administration, the scope of the data administration function, relate the be 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 preserve would-be users from logging-on to the database apportion access to specific parts of the database to specific usersAllocate access to specific operations to specific usersFor 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.