Database Concepts Lecture 3 PDF

Database Concepts Presented by Dr. Amira M. Gaber Lecture 3  Database Design Phases 1. Conceptual design Conceptual modeling is an important phase in designing a successful database application. It sketches out the entities to be represented and determines what k...

Database Concepts Presented by Dr. Amira M. Gaber Lecture 3  Database Design Phases 1. Conceptual design Conceptual modeling is an important phase in designing a successful database application. It sketches out the entities to be represented and determines what kinds of relationships exist between them. It deals with the scope of the database to be created and defines the general rules that need to be considered.  Database Design Phases 2. Logical Design The logical phase of database design is also called the data modeling mapping phase. This phase gives us a result of relation schemas. The basis for these schemas is the ER or the Class Diagram. To create the relation schemas is mainly mechanical operation. There are rules for transferring the ER model or class diagram to relation schemas.  Database Design Phases 3. Normalization Normalization is, in fact, the last piece of the logical design puzzle. The main purpose of normalization is to remove superfluity and every other potential anomaly during the update. Normalization in database design is a way to change the relation schema to reduce any superfluity. With every normalization phase, a new table is added to the database.  Database Design Phases 4. Physical Design The last phase of database design is the physical design phase. In this phase, we implement the database design. Here, a DBMS (Database Management System) must be chosen to use.  Entity Relationship Diagram (ERD) Entity Relationship Diagram (ERD) ERD stands for entity relationship diagram. People also call these types of diagrams ER diagrams and Entity Relationship Models. An ERD visualizes the relationships between entities like people, things, or concepts in a database. An ERD will also often visualize the attributes of these entities. By defining the entities, their attributes, and showing the relationships between them, an ER diagram can illustrate the logical structure of databases.  The Importance of the ERD Document an existing database structure Debug, troubleshoot, and analyze Design a new database Gather design requirements Business process re-engineering (BPR)  Common ERD Symbols An ER diagram has three main components: entities, relationships, and attributes connected by lines. Entities, which are represented by rectangles. An entity is an object or concept about which you want to store information. The Entity should have more than one attribute and more than one entity set. Entity Set: is a set of entities of the same type that share the same properties. A noun is used to represent an entity set. Common ERD Symbols Entity  Common ERD Symbols A weak entity is an entity that must defined by a foreign key relationship with another entity as it cannot be uniquely identified by its own attributes alone. Week entity  Common ERD Symbols Attributes, which are represented by ovals. A key attribute is the unique, distinguishing characteristic of the entity. For example, an employee's social security number might be the employee's key attribute. Attribute  Common ERD Symbols There are types for the attributes A multivalued attribute can have more than one value. For example, an employee entity can have multiple skill values. Languages,hobbies,certificats,,phone number Multi value Attribute  Common ERD Symbols A derived attribute is based on another attribute. For example, an employee's monthly salary is based on the employee's annual salary. Age,total price,body massindex, fullname Derived Attribute  Common ERD Symbols A composite attribute is an attribute where the values of that attribute can be further subdivided into meaningful sub-parts. First Name Last name Name  Common ERD Symbols Mandatory attributes - Mandatory attributes must have a value. For example, in most businesses that track personal information, Name is required. Optional attributes - Optional attributes may have a value or be left null Unique identifier - This type of attribute distinguishes one entity from another. For example, in a classroom, you can distinguish between one student and another using a student ID. This is known as a Key  Common ERD Symbols Key, is a field or a set of fields that has/have a unique value for each record in the relation. You need a key to ensure that you do not meet redundancies within a relation. There are three types of the relationships Candidate key – A candidate key is an attribute or set of attributes that uniquely identifies a record in a relation. Primary key – A primary key is one of the candidate keys from a relation. Every relation must have a primary key. A primary key shall be at least:  Common ERD Symbols Simple keys – these keys have a single attribute. Composite keys – these keys have multiple attributes. Foreign keys – these keys exist usually when there are two or more relations. An attribute from one relation has to exist in the other(s) relation. Relationship sets exist between the two attributes.  Common ERD Symbols Relationships, which are represented by diamond shapes, show how two entities share information in the database. There are three types of the relationships Recursive Relationship which the entities can be self-linked.  Common ERD Symbols Binary Relationship: two entities are connected directly together this called the degree of the relation.  Common ERD Symbols Ternary Relationship: three entities are connected directly together this called the degree of the relation.  Common ERD Symbols Connecting lines, solid lines that connect attributes and show the relationships of entities in the diagram. Name of the Relation Should be verb  Common ERD Symbols Cardinality specifies the numerical attribute of the relationship between entities. It can be one-to-one, many-to-one, or many-to-many  Constraints Constraints Every business has restrictions on which attribute values and which relationships are allowed. In the conceptual data model constraints are used to handle these restrictions. A constraint is a requirement that entity sets must satisfy in a relationship. Constraints may refer to a single attribute of an entity set, or to relationship between entities.  Constraints Domain Integrity: Domain restricts the values of attributes in the relation and is a constraint of the relational model. However, there are real-world semantics for data that cannot be specified if used only with domain constraints. We need more specific ways to state what data values are or are not allowed and which format is suitable for an attribute. For example, the Employee ID (EID) must be unique or the employee Birthdate is in the range [Jan 1, 1950, Jan 1, 2000].  Constraints Entity integrity: To ensure entity integrity, it is required that every table have a primary key. Neither the PK nor any part of it can contain null values. This is because null values for the primary key mean we cannot identify some rows. For example, in the EMPLOYEE table, Phone cannot be a primary key since some people may not have a telephone.  Constraints Referential integrity: Referential integrity requires that a foreign key must have a matching primary key or it must be null. This constraint is specified between two tables (parent and child); it maintains the correspondence between rows in these tables. It means the reference from a row in one table to another table must be valid. Examples of referential integrity constraint in the Customer/Order database of the Company: Customer(CustID, CustName) Order(OrderID, CustID, OrderDate)  ERD Models Conceptual ERD or data model: This model has the most abstraction and least amount of detail, as such it's appropriate for large projects that need a higher level view used by business analysts. A typical conceptual ERD will contain entities and relationships, but offer no details on specific database columns or cardinalities. It's a general, high-level view of database design.  ERD Models Logical ERD or data model: This model adds more detail to the conceptual model by defining additional entities that are operational and transactional. Physical ERD or data model: This model serves as the actual design or blueprint of the database with lots of technical details including defining cardinality and showing primary and foreign keys of entities instead of just their abstract semantic names. For this type of ERD, attributes will often be listed to represent the columns of the real database table.