Database Management Systems

Database Management Systems Module 2

Module Contents • Conceptual modeling • Entity - Relationship concepts • Attributes, Domains, Primary Keys Foreign Keys, ER Diagram and Naming • Structure of relational databases • The relational algebra • tuple and domain relational calculus • Modifying the databases

Conceptual modeling • Conceptual modeling involves building a model of the real world expressed in terms of of the data requirements established. • A conceptual model is a model of the real world expressed in terms of data requirements.

E-R Diagramming/Modeling • One of the major approaches in conceptual modeling is Entity Relationship Modeling/Diagramming (E-R Modeling). • Using ER model, a real world enterprise data can be described in terms of objects and their relationships and it is widely used to develop an initial database design.

E-R Diagramming/Modeling • It provides useful concepts that allow us to move from an informal description of what users want from their database to a more detailed, ad precise, description that can be implemented in a DBMS.

E-R Diagramming/Modeling • Many variations of ER diagrams are in use. • No widely accepted standards prevails.

Overview of Database Design • The database design process can be divided into six steps. • The ER model is most relevant to the first three steps. • Requirement analysis • Conceptual Database Design • Logical Database Design

Requirement analysis • The very first step in designing database application is to understand: • what data is to be stored in the database • what application must be built on top of it • and what operations are most frequent and subject to performance requirements. • Understand what users want from the database.

Requirement analysis • It is an informal process that involves discussions with user groups. • Several methodologies have been proposed for organising and presenting the information gathered in this step.

Conceptual Database Design • The information gathered in the requirements analysis step is used to develop a high level description of the data to be stored in the database, along with the constraints that are known to hold over this. • This step can be carried out using ER Model.

Logical Database Design • A process of choosing DBMS to implement database design, and converting the conceptual database design into a database schema in the data model of the chosen DBMS. • In case of relational DBMS this step involve converting an ER schem into a relational database schema.

Schema Refinement • This is the fourth step in database design where analysis of the collection of relations in relational database schema is done, and also it identifies potential problems and refines it. • Schema refinement can be guided by some elegant and powerful theory (Normalisation of relations).

Physical Database Design • Further refine the database design to ensure that it meets desired performance criteria. • This step may involve building indexes in some tables.

Security Design • In this step, we identify different user groups and different roles played by various users (e.g., the development team for a product, the customer support representatives, product manager) • For each role and user group, we must ensure that users can access only necessary part of the Database.

Fundamentals of E-R Diagramming • We assume that our relational data model is represented using the constructs of the E-R approach. • In this approach a given universe of discourse is represented using an entity model: a model built up of entities, relationships and attributes.

Entities • An entity can be defined as a thing which an organisation recognises as being of an independent existence and which can be uniquely identified. • It is an aspect of the real world which can be distinguished from other aspects of the real world.

Entities • It can be a physical object such as: • a house or a car, an event such as a house sale or a car service, or a concept such as customer transaction or order.

Entities • There is a real difference between an entity and an entity-type. • An entity-type is a category. • An entity is an instance of a given entity type. There are usually many instances of an entity type.

Entities • Example: • A student is an entity-type whereas Juma is an instance of this entity-type. • Juma is an entity • Because the term entity-type is some what cumbersome, most people tend to use the term entity as synonym for this.

Entities • But remember that when it is referred to an entity we normally mean an entity-type. • An entity is described using a set of attributes. • A collection of similar entities is called an entity – set • All entities in a given entity-set have the same attribute.

Entities • A collection of similar entities is called an entity – set • All entities in a given entity-set have the same attribute.

Attributes • As a real world aspect, an entity is characterised by a number of properties or attributes. • Values assigned to attributes are used to distinguish one entity from another. • E.g deptName and Location are both attributes which defines the entity Department

Attributes • Our choice of attributes reflects the level of detail at which we wish to represent information about entities. For example the Employee entity set could use name, social-security number (ssn) as attributes.

Domain • For each attribute associated with an entity set we must identify a Domain of possible values. • For example the domain associated with the attribute name of Employees might be the set of 20 – character string.

Key • For each entity set you have to choose a key. • A key is a minimal set of attributes whose values uniquely identify an entity in the set. • There could be more than one candidate key; design a primary key.

Relationships • A relationship is some association between entities. • The focus is on binary relationships, which is associations between two entities. • In E-R approach more than one relationship can exist between any two entites

Relationships • Example: • The entities House and Person can be related by ownership and/or by occupation. • The objective of the E-R approach is to document only direct relationships • E.g direct relationships exists between the entities Parent and Child and between Child and School. The relationship between Parent and School is indirect: it exist only by virtue of the Child entity

Relationships Note: • You must be clear of what you are attempting to represent. • A relationship set can be thought of as set of n-tuples. • Each n-tuple denotes a relationship involving n entities e1 through en, where entity ei is an entity set Ei.

Relationships • Below is the relationship set Works_In, in which each relationship indicates a department in which an employee works since name age dame budget ssn deptID Works_In Employees Departments

Ternary Relationship • This occur when association is between more than two entities. • E.g. When each department has different offices in several locations at which each employee works since name age dame budget ssn deptID Works_In Employees Departments Locations address capacity

Ternary Relationship • This relationship is ternary because we must record an association between an employee, a department, and a location.

Degree of Cardinality • This refer to level of relationship between one entity and another. • One-to-One – Only one entity is associated with a single entity of another entity set. E.g. employee-dept. • One-to-Many – Indicate one entity can be associated with many entities of another entity set. E.g. Dept->Employee • Many-to-Many – Indicate that many entities from one entity set is associated with many entity from another entity set. E.g. employee->dept, dept->employee

Class Work

Database Management Systems