The RELATIONAL MODEL.
entity classes, a set of attributes labeling entity classes or relationships, etc. . developers as well as frequent and effective feedbacks, customers may eventually as a defined one (such as waterfall model), the process will lack adaptability. Contents For each attribute of an entity type, you can record the following Outstanding Orders Value -- The Outstanding Orders Value for a and relationship memberships, it is worth considering defining a subtype or. The ER model defines the conceptual view of a database. It works around An entity set may contain entities with attribute sharing similar values. For example.
We could delete all of the rows and the table would still exist. The shorthand notation to express the structure of a table is: We underline the primary key. A table can have an unlimited number of foreign keys linking to other tables.
We use a dashed underline for foreign keys. Properties of Tables Relations: Each table in a database has a unique name. No multivalued fields are allowed in a table. Each row is unique.
Each column has a unique field name. The sequence of rows as well as columns is insignificant and can be interchanged without changing the data. Merge tables with the same primary key based on the existing database schema for the firm - the system you are currently designing will probably add to an existing schema. In transforming entities into tables, recall there are three types of entities: These may be gerunds. Transform this entity to a relational table structure by creating a single table with a column for each attribute and the primary key attribute underlined as the primary key column for the table.
The correct type of relationship line is drawn, the right way round! Verbs or phrases are added to aid understanding. Many-to-many relationships The symbol used to represent a many-to-many relationship is: Here is an example of a many-to-many relationship: Using the same method as before, we need to describe the relationship with two sentences: Each pupil studies many GCSEs.
Entity-Relationship (E-R) diagrams
Each GCSE is studied by many pupils. How would you implement this database? To help us understand the design, we will put some records in the tables, as before. This is a far from ideal solution! For a start, how many attributes will you need in the GCSE table? You could have many indeed if there were lots of pupils taking a particular GCSE. More importantly, however, is that you or rather Access or whichever software you are using are going to have a problem recombining the two tables back into one!
For example, a record in the GCSE table doesn't match to just one record in the pupil table. One GCSE will match back to many different records in the pupil table. This is not good! Resolving many-to-many relationships Fortunately, the solution is straightforward. It is known as 'resolving a many-to-many relationship'. If you have a many-to-many relationship, you can turn it into two one-to-many relationships by creating an extra linking table.
In other words, this: A simple primary key is a primary key that is made up of only one attribute. A compound primary key is a primary key that is made up of more than one attribute.
Reading the relationships, we have: Each record in the Pupil table i. Have a look at pupil number one. This record is related to only one record in the Pupil table. This particular record relates only to pupil number 2. It appears in the second record and in the last one. We have resolved our many-to-many relationship. We store the details about each GCSE only once.
This is also good! We have a third table that matches up each pupil to every GCSE they are taking. This table has a compound primary key made up of the primary key from the pupil table and the primary key from the GCSE table. Each compound primary key combination is unique! Each row is unique - you cannot have two rows the same. At any time, we can recombine the data from any related records from the three tables into one record!
One-to-one relationships This type of relationship is shown with a simple straight line.
An example of this kind of relationship is shown below: This kind of relationship is used to keep the database design compact and clear. Some of those products are CDs and some are, for example, fish! If you are keeping information attributes about the products, one possible set of attributes for the PRODUCT table might be the following: Of course, the number of attributes needed could be very large.
They will include items such as clothes, insurance, cars, fresh vegetables and so on.
Each of these products will need their own specialist attributes. For example, you might need an attribute to hold the size of clothes. Clearly, as you fill out this table with records of each actual product, some of these attributes will be left blank.
So not only will you have a table with a great many different attributes, many of them for a particular product will be left blank.
ER Model Basic Concepts
This is not the clearest of designs! One entity would be purely for CDs.
One would be purely for fish. Another would be for clothes, and so on.
Entity-Relationship (E-R) diagrams notes
The result of using one-to-one relationships in a database is a clearer design. Producing E-R diagrams from the beginning Now you know how to interpret E-R diagrams, you can practice putting them together from the start. You would begin by investigating whatever system you have been asked to design a database for. You could do this amongst other methods by interviewing people individually, carrying out group interviews, collecting example documents, observing people using the system or sending out questionnaires by email, fax or post.
The next step would then be to write down how the system works. A library database example A library contains books! Pupils take out up to six books from the library. There may be more than one copy of a particular book.