Download MMA - DATABASE & ERD and more Lecture notes Multimedia Applications in PDF only on Docsity!
MMS 144 Principles of Multimedia Information Management
Second Trimester AY 2024-
Module 2: Data Models
Learning Outcomes
At the end of this module, the students should be able to:
- Define the Entity-Relationship (ER) model and understand the terminology used in ER modeling.
- Differentiate the different attribute types.
- Identify and explain the various ER constraints.
- Identify how to construct an ER diagram.
- Explain the core concept of the Relational Model.
- Demonstrate how to transform an ER Diagram into a Relational Model.
Data Models
A core principle of the database approach is data abstraction , which simplifies data by hiding storage and organization details while emphasizing essential features. This allows users to view data at their preferred level of complexity. A data model is a logical blueprint for a database. It simplifies database design by defining core elements like entities, attributes, relationships, and constraints, which a DBMS uses. Data models provide basic retrieval/update operations, and are essential for achieving this abstraction.
Data models, which describe database structure, vary depending on the concepts they utilize. While the network, hierarchical, and relational models are the most common, other data models exist to address specific real-world scenarios. We will focus on High-level or Conceptual data models that represent real-world concepts using entities, attributes, and relationships. Entities are objects like "employee" or "project." Attributes are their properties, such as "employee name" or "salary." Relationships show how entities connect as an "employee works on a project" relationship.
Using High-Level Conceptual Data Models for Database Design
Conceptual modeling is crucial for successful database application design. A database application includes the database itself and the programs that manage queries and updates. For instance, a Student Enrollment application uses programs to handle course enlisting and schedule finalization, providing user-friendly interfaces for students. Designing, implementing, and testing these programs is a significant part of the application. While traditionally seen as software engineering, database design and software engineering are closely linked and often integrated in modern software design tools.
Figure 1 provides a simplified overview of the database design process, which emphasizes the use of high-level conceptual database models.
- Requirements Collection and Analysis. This initial phase focuses on understanding user needs and documenting data requirements. It ensures designers grasp the users' perspectives.
- Data Requirements and Functional Requirements. The outcome of the first step, includes both data requirements (what data is needed) and functional requirements (what the system should do).
- Conceptual Design. This stage involves creating a high-level outline of the database schema, and defining the key information to be stored.
- Conceptual Schema. The result of conceptual design. This is a detailed description of data requirements, including entities, relationships, and constraints.
- Functional Analysis. This step defines how users will interact with the database, specifying operations and transactions that will be implemented.
- High-Level Transaction Specification. The output of functional analysis. This provides software developers with a guide for implementing database transactions within the application.
- Logical Design (Data Model Mapping). This stage involves the actual database creation using a DBMS, frequently aided by automated database design tools.
- Physical Design. This stage focuses on the physical implementation of the database, including storage structures, file organization, indexing, access paths, and other physical design parameters.
Figure 2: Graphical representations of an entity.
Entity Types
- Strong Entity ● An entity that exists independently. ● Always has a primary key. ● Visually represented by a single rectangle.
- Weak Entity ● An entity whose existence relies on a strong entity. ● Cannot form a primary key on its own; depends on the strong entity for identification. ● May have a partial key. ● Visually represented by a double rectangle.
Attribute Types
- Composite Attributes ● Composite attributes are like containers that hold other attributes within them. They are useful when you need to represent a concept that has multiple parts. It can be broken down into smaller, more basic attributes, each with its own independent meaning. ● Examples: ○ Full Name: Instead of just "Name," you might use "Full Name" as a composite attribute. This could be further divided into "First Name," "Middle Name," and "Last Name." This is helpful if you need to sort or search by last name, or if you want to address someone more formally. ○ Location: Beyond just "Address," you could have a "Location" composite attribute containing "Latitude" and "Longitude" for precise geographical data. ○ Date: "Date" could be broken into "Year," "Month," and "Day."
- Simple (Atomic) Attributes ● A simple attribute is a single, indivisible piece of information. These attributes are the most basic units of data. They cannot be further subdivided. ● Examples: ○ Employee ID: A unique number assigned to each employee. ○ Product Price: A single numerical value representing the cost of a product. ○ Boolean value: True or false. ○ Email address: A single string.
- Single-Valued Attributes ● A single-valued attribute can have only one value at any given time for a specific instance of an entity. ● Examples: ○ Social Security Number (SSN): Each person has only one SSN. ○ License Plate: Each car has a unique license plate. ○ Birthdate: A person has only one birthdate.
- Multivalued Attributes ● A multivalued attribute can have a set of values (multiple values) for a single instance of an entity. ● Examples: ○ Phone Numbers: A person might have a home phone, a mobile phone, and a work phone. ○ Skills: An employee might possess multiple skills (e.g., programming languages, communication skills). ○ Hobbies: A person might have several hobbies. ○ Dependent names: An employee might have more than one dependent.
- Stored Attributes ● A stored attribute's value is explicitly entered and maintained in the database. These attributes are directly stored in the database. ● Examples: ○ Date of Hire: The actual date when an employee was hired. ○ Customer Order Date: The date when a customer places an order. ○ Product weight: The weight of a product.
- Derived Attributes ● A derived attribute's value is computed or calculated based on other stored attributes or related entities.
They are used to connect related information between entities. It is the association among different entities.
The number of entities involved in a relationship is its degree. A relationship type can be degree two (binary) or degree three (ternary).
Relationships form relationship types between entity types (e.g. Relationship type Enrolls between entity types Student and Subject).
A relationship may be characterized by attributes in the same way as an entity (e.g. date and quantity in the Order relationship type).
A Relationship can be: ● A strong relationship if it connects two independent entities (strong entities). It is represented by a single diamond. ● A weak relationship if it connects a weak entity to its identifying strong entity. It is represented by a double diamond.
Figure 4: Binary relationship between the Student and Subject entities. An example of a strong relationship as well.
Figure 5: Ternary relationship between the Teacher, Subject, and Student entities. Another example of a strong relationship.
Figure 5: Binary relationship between a strong entity (Employee) and a weak entity (Dependent).An example of a weak relationship as well. The attribute Name of the Dependent is a partial key.
Figure 6: Binary relationship between a Customer entity and a Product entity. The relationship has an attribute Date and Quantity.
ER Constraints ER Constraints define rules for how entities relate to each other:
- Cardinality Constraint. Specifies the number of entity instances involved in a relationship. ● One-to-One (1:1): Each entity instance is related to at most one instance of the other entity. ● One-to-Many (1:N) or Many-to-One (N:1): One entity instance can relate to many instances of the other, but each instance of the "many" side relates to at most one of the "one" side. ● Many-to-Many (M:N): Entity instances on both sides can relate to multiple instances of the other.
- Participation Constraint. Defines whether entity instances must participate in a relationship. ● Total Participation (Mandatory): Every entity instance must participate. Represented by double lines. ● Partial Participation (Optional): Entity instances may or may not participate. Represented by single lines. The minimum number of relationships an entity can be a part of is zero (0) or one (1).
Partial Key Attribute
Composite Attribute
Participation Constraints (Total Participation of E2 in R)
Cardinality Constraints (Cardinality Ratio 1: N for E1:E2 in R)
NOTE: Different notation systems exist for ER diagrams. Please use the notation from this handout for the assignments.
Simple Example A subject is defined by a unique subject ID, a subject title, and the number of units. Crucially, a course must always have students enrolled. Each student is uniquely identified by a student number, and their name is further specified by first name, middle name, and last name. Additional student information includes their birthday, from which their age is derived, and their contact numbers, which can have multiple entries. A student cannot be considered a student unless they are enrolled in at least one subject. A single subject can have multiple students, and conversely, a student can be enrolled in different subjects.
The Relational Model
The relational model is a formal, logical data model. It was proposed by Codd in 1970 [1] and offers very strong theoretical backing through relational algebra.
The relational model is the base of relational DBMSes (PostgreSQL, MySQL, SQL Server, etc.) and is implemented through SQL. It is also the base of the normalization theory.
The relational model structures databases as a collection of relations (not relationships), which are conceptually similar to tables. It organizes database elements into tables, where each table represents a set of data, enabling various operations to be performed on that data.
Components of a Relational Table
- Relations ● Relations can be viewed as a flat file , or table representing the data and identified by a name. It is called a flat file because each record has a simple linear or flat structure. ● A Table is called a relation in formal terms of the relational model. A database is composed of multiple tables and each table holds the data.
Figure 7: A database that contains three tables.
- Column ● The column headers of a relation are called attributes. They are the principal storage units in databases. They house the basic components of data into which your content can be broken down. The number of attributes in a relation is called its degree. ● Each attribute is characterized by its domain. A domain is the original set of atomic values used to model data. It is a set of acceptable values that a column is allowed to contain. Atomic value means that each value in the domain is indivisible or cannot be divided.
Properties of a Table ● It has a name that is distinct from all other tables in the database. ● There are no duplicate rows; each row is distinct. ● Entries in columns are atomic, which means that the table does not contain repeating groups or multivalued attributes. ● Entries from columns are from the same domain based on their data type including: ○ Number (numeric, integer, float, etc.) ○ Character (string) ○ Boolean (true or false) ● Operations combining different data types are not allowed. ● Each attribute has a distinct name. ● The sequence of columns is insignificant. ● The sequence of rows is insignificant
Keys ● A superkey is any set of attributes {Ai}i∈{0..n} of a relation where no two tuples can have the same set of values for these attributes. i.e. a superkey is any set of attributes that uniquely identifies tuples. ● A candidate key is a minimal superkey, i.e. any superkey so that there is no smaller superkey. ● The primary key of a relation is a particular candidate key used to identify tuples. The primary key is represented by underlining its attributes. Every relation must have a primary key. ● A foreign key is an attribute that references the primary key of another relation.
Figure 9: Pnumber and Dnumber are the primary keys of their respective tables. Dnum is a foreign key in the Project table that references Dnumber from the Department table.
Integrity Constraints A database schema is the sum of the relation schemas and integrity constraints or the rules to make sure the database is in a coherent state. The different kinds of constraints are: ● Entity Integrity Constraint. specifies that a primary key’s value cannot be NULL. ● Domain Integrity Constraint. specifies that an attribute’s value must respect its domain. ● Referential Integrity Constraint. enforces the foreign key mechanism ● Other Constraints. other constraints can be specified in the database using mechanisms such as triggers or assertions.
Conversion of ER Diagram to Relational Model
When designing a database, we first draft a conceptual model with the ER model before designing the physical relational model with the logical relational model.
This process echoes the levels of abstraction in the previous topic.
- Design the conceptual model with the end user/stakeholders, who know what information they need but do not care about how the database is implemented.
- Make a logical relational model
- Design a physical relational model by adding the domains of the attributes
With the given ER diagram, will now see how to transform our ER model into a relational model with the following steps.
STEP 3
● For one-to-many or many-to-one relationships between two strong entities: ○ Take the primary key from the entity representing the "one" side. ○ Add it as a foreign key to the entity representing the "many" side.
PROFESSOR(EmpNumber, FirstName, MiddleName, LastName, RankNumber) RANK(RankNumber, RankTitle)
PROFESSOR and RANK have a many-to-one cardinality. With that, we add the primary key of RANK as a foreign key in PROFESSOR.
● When two entities have a many-to-many relationship:
- Create a new relation specifically for this relationship.
- The relation’s name should reflect the relationship itself.
- Add the primary keys from both original entities as foreign keys in this new relation.
- If the relationship has its own attributes, include them as columns in this relation as well.
ENROLLS(StudentNumber, SubjectId) TEACHES(EmpNumber, SubjectId)
PROFESSOR and SUBJECT as well as STUDENT and SUBJECT have a many-to-many cardinality. With that, we create a new relation for each relationship and include the primary keys as foreign keys in the new relation.
Final Relational Schema ● PROFESSOR(EmpNumber, FirstName, MiddleName, LastName, RankNumber) ● RANK(RankNumber, RankTitle) ● STUDENT(StudentNumber, FirstName, MiddleName, LastName, Age) ● SUBJECT(SubjectId, SubjectTitle) ● SUBJECT_PREREQUISITE(SubjectId, Prerequisite) ● ENROLLS(StudentNumber, SubjectId) ● TEACHES(EmpNumber, SubjectId)
Logical Relational Model The resulting Logical Relational Model based on the Final Relational Schema is given below. The foreign keys are pointing to the primary keys from their original table (e.g. RankNumber in the PROFESSOR table is a foreign key that points to the RankNumber primary key in the RANK table). Each primary key is underlined.
Physical Relational Model The resulting Physical Relational Model based on the Logical Relational Model is given below.
