Designing a database

Database applications

• Usually if we speak about a database application we mean two things:

– the DBMS and

– the associated programs that make the DBMS more user friendly

• Part of designing a database is deciding on these application programs

• Traditionally, the design and testing of application programs has been more software engineering than database

• We will concentrate more on specifying the database structures and constraints during db design.

Using a data model

• A data model is the bridge between real-world objects and the database records that reside on a computer disk.

• It is used to abstract all the details of the implementation so we can look at the big picture.

• We will look at the Entity-Relational (ER) model

– This a high-level model that non computer scientists can understand and help develop

• The relational model is a mid-level model

Database Design Process

• Step 1: Collect the requirements and analyze them

• Interview the client to understand and document their data requirements

• This should be as concise and detailed as possible

• Separate from the data requirements are the operations that need to be applied to the DB

– These are called the functional requirements

– These will include user defined operations as well as retrievals and updates

– what you get: The DB requirements

• Step 2: Create a Conceptual DB design. We will use an E-R diagram here.

• This should include detailed descriptions of :

– The entity types

– Relationships

– Constraints

• These are expressed using the concepts provided by the E-R model

• You and the end used can work on this diagram together to be sure the model includes all the data and constraints

– what you get: The Conceptual Schema

• Step 3: Use a DBMS to create the logic design in the computer

• An implementation data model is used by the DBMS

• This will be the relational model in our case

• The conceptual schema from the E-R model is transformed into the relational model

– What you get: Implemented Relational Schema

• Step 4: Specify the internal data structures

– What you get: The data structures stored on disk

• We are focusing on step 2 in this chapter.

The ER model and ER diagram

• The ER model is a high level conceptual data model

• It is often expressed as an ER diagram

• In an ER diagram, the structure and constrains to the db system can be expressed

• An ER diagram can be mapped into the relations of the relational model

– Jus follow some very well defined rules to go from a ER diagram to the relations

E-R Diagrams--Entities

• An entity is some real-world object about which we want to keep information.

• Each entity has attributes – the properties that describe it

– A particular entity will have values for most of its attributes

– These values of entity attributes are a major part of the database

E-R Diagrams--attributes

• There are several types of attributes:

– Simple or composite

• These can form a hierarchy

– Single-valued or multivalued

• Some attributes can have more than one value

– Stored or derived

• The value of an attribute may be null

• An entity must have an attribute or set of attributes that is unique. This is the primary key.

An example database – the University

• What are the entities for our university?

– An entity type is a rectangular box in an E-R diagram

• What are the attributes for each of our entities?

– Attributes are ovals, and are attached to their entity type by a straight line.

• Which of our specifications have we not represented yet?

E-R Diagrams

Other ER diagram notations

– Composite attributes are attached to the components by a straight line

– Multivalued attributes use double ovals

– Key attributes have their name underlined

• If an entity has no primary key, it is called a weak entity type, indicated by a double rectangle

The Company E-R diagram

• The E-R diagram is derived from the description of the database obtained from the client.

• It is usually built in several steps

– First, tentatively find the entities.

– Next, put in the attributes for each entity.

– Find the relationships by looking for attributes that refer to other relations

Departments

• The company is organized into departments.

• Each department has a unique name, a unique number and a particular employee who manages the department

• We keep track of the start date when that employee began managing the department

• A department may have several locations

Projects

• A department controls a number of projects.

• Each project has a unique name, a unique number and a single location.

Employee

• We store each employee’s name, social security number, address, salary, sex and birth date

• An employee is assigned to one department but may work on several projects which are not necessarily controlled by the same department.

• We keep track of the number of hours per week that an employee works on each project

• We also keep track of the direct supervisor of each employee

Dependent

• We want to keep track of the dependents of each employee for insurance purposes.

• We keep each dependent’s first name, sex, birth date, and relationship to the employee.

Relationships

• Whenever an attribute of one entity type refers to another entity type, some relationship exists.

• In an E-R diagram these references should be represented as relationships, not as attributes

• In the initial design of entity types, what first are attributes are later reclassified as relationships

• Which of our attributes are relationships?

– They are indicated by diamonds in an E-R diagram

Attributes of Relationships

• Relationships can have attributes just like entities can.

• Where would you put the hours an employee works on a certain project?

– It in not really an attribute of employee or project.

– Logically, it is an attribute of the works_on relationship.

– What other attributes of a relationship is there in the company DB?

The company E-R diagram

• A few things to notice

– Four entities

– Six relationships

• The Employee entity enters into five of these six

– The supervision relationship is a relationship of Employee to Employee

– There are two relationships between Employee and Department

– Dependent is a weak entity, since it has no primary key

Naming

• Usually the nouns in the database design are entities

• The relationships are usually verbs

• Attributes are usually also nouns

• Often names are chosen to make the E-R diagram read from left to right, top to bottom

Designing E-R diagrams hints

• Relationships are usually modeled first as attributes

• An attribute that exists in several entity types may be refined into it own entity type.

• If an entity has a single attribute in the original design, it may belong as an attribute of another entity.

Weak Entity types

• Weak entities are identified by being related to specific entities from another entity.

• They do not have a primary key attribute of their own.

• They always have total participation with the owner entity.

Constraints on relationships

• Usually a relationship has some constraints that limit the combinations of entities.

• These constraints are contained in the design requirements from the client, and should be indicated in the model.

• There are two main type of relationship constraints:

– Cardinality ratio

– Participation

Cardinality Constraints

• Cardinality ratio

– This is the number of relationship instances that an entity can participate in

• One to many (1:N)

• Many to many (N:M)

• One to one (1:1)

– These are entered on the E-R diagram by putting a 1, N or M next to the relationship diamond

Example: works_for relationship

– An employee enters into a relationship with how many departments?

• An employee works for one department

– One department enters into a relationship with how many employees?

• One department has many employees

• works_on relationship

– An employee enters into a relationship with how many projects?

• An employee may work on many projects

– A project enters into a relationship with how many employees?

• A project has many employees

Participation Constraints

– This specifies whether the existence of an entity depends on its being related to the other entity.

– There are two types of participation constraints

• Total and partial

– If the specs say every employee must work for a department, then that employee can exist only if they work for a department. This is total participation.

• Indicated by a double line on the E-R diagram

– Does every employee have to participate in the manages relationship? It is partial participation.

• Indicated by a single line on the E-R diagram

E-R to Relational mapping

• Converting the E-R diagram to the relational model is a very cut-and-dried/rote operation

– Just follow the rules

– Page 192-199

• For each regular entity type E

– Create a relation (table) that includes all the simple attributes of E

– Do not include any multivalued attributes.

– Choose a Primary Key from the key attributes.

• For each weak entity type W

– Create a table that includes all the attributes of W

– Add to this the PK of the owner entity type as a foreign key

– The primary key of this table is the PK of the owner plus the partial key of W

• For each binary 1:1 relationship type of R

– Identify the tables that correspond to the entity types entering into the relationship

– Choose one of the tables and include in its attributes the PK of the other table as a foreign key
If possible choose the table that has total participation in the relationship

– Include any attributes of the relationship

• For each binary 1:N relationship type R

– Identify the table that corresponds to the entity type on the N-side of the relationship

– Include in its attributes the PK of the table on the 1-side

• For each binary N:M relationship type R

– Create a new table to represent R

– Include as FK attributes the PK of the tables that represent the participating entity types

– These PKs will form the PK of the new table

– Include any attributes of the relationship

• For each multivalued attribute A

– Create a new table R

– One attribute will be the multivalued attribute

– Another attribute will be the primary key of the entity that has A as an attribute