The Entity Relationship Diagram

The Entity kin DiagramDesign the selective informationbase system for P etc.re. Petc ar is a medium sized veterinary surgery with sextette branches across London. They want a database system developed to handle the immortalizes of the animals they care for, prescriptions and appointments.Entity-relationship diagramAn entity-relationship diagram (ERD) is a data modeling technique that creates a lifelike representation of the entities, and the relationships between entities, within an in leapation system. This diagram is often hired as a way to visualize a relational database individually(prenominal) entity represents a database get across, and the relationship lines represent the observes in one table that point to specific records in connect tables. ERDs may as well be more abstract, non needfully capturing every table needed within a database, but serving to diagram the major concepts and relationships. commentary of relationshipA relationship is some connection betwee n entities. Relationship is shown by line between entities. Relationship lines indicate that each illustrate of an entity may have a relationship with instances of the connected entity, or vice versa.Definition of entity attributeAn entity is characterized by a be of properties or attributes. Values assigned to attributes are used to distinguish one entity from an other.Definition of Optionality and CardinalitySymbols 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 tramp define as a thing which an organization recognizes as being capable of an independent existence and which terminate be uniquely identified. In addition, an entity is an induction from the complexities of some domain. Each entity is shown in box w ithin the ERD.In this case study, the entities of the Veterinary Surgery are identified as followedOwner is correspond possessors of animals which implicate name, address, home telephone number and mobile telephone numberAnimal is represented randomness of an animal which included sex, age and ownerBreed is represented information of breed for each animal fictitious characterAnimalType is represented type of animal (dog, cat, rab number, etc) involution 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 construe of the appointment, the diagnosis made and the charge made for the appointmentPrescription is represented record shows the period, in days, that the drug mustiness be taken for. The cost of the medication is in addition recordedDrug contains the name of the drugDrugType 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 wooden leg 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 modelRelationships 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 RelationshipOne-to-Many Entity Relationship is by far the most everyday relationship type. It consists of either a one through many or a zero through many nota tions 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.OwnerBreedAnimalPrescriptionDrugDrugTypeAnimalTypeAppointmentVetAnimalTypeVetBranchFig 1 entity relationship diagram for the Petcare. standardized TablesTable is a data ( take to be), 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 sub clan of the values in the column, which has been identified as a expectation keystone.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 material data rows, the panels are generally associated with some other meta-data, such as restrictions on the table or the values in columns.Primary keyPrimary key is a field or combination of fields that uniquely identify a record in the table, so each trail 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 impart never be duplicated in the comparable 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 go out constantly be a record for each customer, and the Order table has a record for each order that the customer has.For eign keysA unconnected 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 uniqueDefinition of standardisationDatabase conventionalismization 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 limpid 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 under attack(predicate) to a sufficiently saneized 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. approximately 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 affectation of simple ideas. All too often, the simplicity is lost in esoteric terminology and papers are often excessively concerned with the formalism and generate very practical insight.In this project, why need a blueprintization 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 desig n, The designer interviews exploiters 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 formTo 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 keyTake 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 pasture 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 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 twisting of the legal Oath presented belowNo repeating groupThe data items depended upon the key.The while key And nothing but the keySo 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 panorama key. The definition of BCNF is treatment 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 con forming 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 oerlap. For example, the candidate keys have at least one jet attribute.Here is all tables after normalizationOwnerIDName sendHomePhoneNumberMobileTable 2.11 VetAnimalTypeUsing a Database Management System (database management system) set-up all of the above normalised tablesA Database Management System (DBMS) is a set of computer software program 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 acce ss 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 organizations 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 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 logi cal 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, tribute 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, pro gramming languages, and interfaces.Microsoft Access 2003 to setup normalized tables and designed test data.Set-up and test all of the following queries apply Structured motion 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 viewing 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 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 inser t 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 break the query in the database you have developedDisplay 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.AnimalTypeNameFROM (AnimalType INNER JOIN VetAnimalType ON AnimalType.AnimalTypeID = VetAnimalType.Anima lTypeID) 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 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 proletariat 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 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 relationshi p 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 generalisation 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 duet the two worlds.The core of object-relational thinking is the great power 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. Eac h 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 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. in time 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 garner during the analysis phase, but will also usually support a broader class of requirements They are more stable in that, as ne w requirements arise, the models will require fewer changes than traditional modelsData is not information unless it is valued. Information value provides profit or gain only when accessible or used. availableness 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. T his 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 thingsPrevent would-be users from logging-on to the databaseAllocate access to specific parts of the database to specific usersAllocate access to specific operations to specific usersFor the DBMS toolkit to follow-up the datab ase, 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.