Database Systems Development Process Overview
Explore the process of developing a database system, including high-level representation with E-R diagrams, converting diagrams into tables, developing operations, interface design, normalization, schema definition, and testing. Key chapters covered are 3, 4, 6-9, and 2.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
CSCE-608 Database Systems Spring 2025 Instructor: Jianer Chen Office: PETR 428 Phone: 845-4259 Email: chen@cse.tamu.edu Notes 3: Entity/Relationship Diagrams
Process of Database Development Description of the database application High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 4 Chapters 6-8 Developing database application user interface Relation normalization Chapter 3 Chapter 9 Defining database schema (using DDL) Testing Chapter 2 2
Process of Database Development Description of the database application High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 4 Chapters 6-8 Developing database application user interface Relation normalization Chapter 3 Chapter 9 Defining database schema (using DDL) Testing Chapter 2 3
Process of Database Development Description of the database application High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 4 Chapters 6-8 Developing database application user interface Relation normalization Chapter 3 Chapter 9 Defining database schema (using DDL) Testing Chapter 2 4
Process of Database Development Description of the database application High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 4 Chapters 6-8 Developing database application user interface Relation normalization Chapter 3 Chapter 9 Defining database schema (using DDL) Testing Chapter 2 5
Process of Database Development Description of the database application High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 4 Chapters 6-8 Developing database application user interface Relation normalization Chapter 3 Chapter 9 Defining database schema (using DDL) Testing Chapter 2 6
Process of Database Development Description of the database application High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 4 Chapters 6-8 Developing database application user interface Relation normalization Chapter 3 Chapter 9 Defining database schema (using DDL) Testing Chapter 2 7
Process of Database Development Description of the database application High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 4 Chapters 6-8 Developing database application user interface Relation normalization Chapter 3 Chapter 9 Defining database schema (using DDL) Testing Chapter 2 8
Process of Database Development Description of the database application High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 4 Chapters 6-8 Developing database application user interface Relation normalization Chapter 3 Chapter 9 Defining database schema (using DDL) Testing Chapter 2 9
Process of Database Development Description of the database application High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 4 Chapters 6-8 Developing database application user interface Relation normalization Chapter 3 Chapter 9 Defining database schema (using DDL) Testing Chapter 2 10
Process of Database Development Description of the database application High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 4 Chapters 6-8 Developing database application user interface Relation normalization Chapter 3 Chapter 9 Defining database schema (using DDL) Testing Chapter 2 11
The Entity/Relationship Model The E/R model is an intuitive (graphical) model used to sketch database designs, (where entity = object). Properties of entities and how entities connect. Not how to operate on entities. 12
The Entity/Relationship Model The E/R model is an intuitive (graphical) model used to sketch database designs, (where entity = object). Properties of entities and how entities connect. Not how to operate on entities. E/R designs are given by pictures (entity/relationship diagrams). 13
The Entity/Relationship Model The E/R model is an intuitive (graphical) model used to sketch database designs, (where entity = object). Properties of entities and how entities connect. Not how to operate on entities. E/R designs are given by pictures (entity/relationship diagrams). It is in general straightforward to convert E/R designs to relational database designs. 14
Entity Sets Entity = thing or object. Entity set = collection of similar entities. Similar to a class in object-oriented languages. Attribute = property of (the entities of) an entity set. Attributes are of simple data types, e.g. integers or character strings. 15
E/R Diagrams In an entity-relationship diagram: Entity set = rectangle. Attribute = oval, with a line to the rectangle representing its entity set. 16
E/R Diagrams In an entity-relationship diagram: Entity set = rectangle. Attribute = oval, with a line to the rectangle representing its entity set. Example Entity set Beers has two attributes, name and manf (manufacturer) Each Beers entity has values for these two attributes. e.g. (Bud, Anheuser-Busch) name manf Beers 17
Relationships A relationship connects some entity sets. It is given by a diamond, with lines to each of the entity sets involved. 18
Relationships A relationship connects some entity sets. It is given by a diamond, with lines to each of the entity sets involved. name manf name addr Bars Beers license Drinkers name addr 19
Relationships A relationship connects some entity sets. It is given by a diamond, with lines to each of the entity sets involved. Bars sell some beers. name manf name addr Bars Beers Sells license Drinkers name addr 20
Relationships A relationship connects some entity sets. It is given by a diamond, with lines to each of the entity sets involved. Bars sell some beers. name manf name addr Bars Beers Sells license Drinkers like some beers. Likes Drinkers name addr 21
Relationships A relationship connects some entity sets. It is given by a diamond, with lines to each of the entity sets involved. Bars sell some beers. name manf name addr Bars Beers Sells license Drinkers like some beers. Frequents Likes Drinkers frequent some bars. Drinkers name addr 22
Relationship Set The current value of an entity set is the set of entities that belong to it. Example: the set of all bars in our database. The value of a relationship is a set of lists of currently related entities, one from each of the related entity sets. Bar Joe s Bar Joe s Bar Sue s Bar Sue s Bar Sue s Bar Beer Bud Miller Bud Pete s Ale Bud Lite For the relationship Sells, we might have a relationship set like: 23
Multiway Relationships Sometimes, we need a relationship that connects more than two entity sets. 24
Multiway Relationships Sometimes, we need a relationship that connects more than two entity sets. Example. Suppose that drinkers will only drink certain beers at certain bars. 25
Multiway Relationships Sometimes, we need a relationship that connects more than two entity sets. Example. Suppose that drinkers will only drink certain beers at certain bars. Our three binary relationships Likes, Sells, and Frequents do not allow us to make this distinction. But a 3-way relationship would. 26
Multiway Relationships Sometimes, we need a relationship that connects more than two entity sets. Example. Suppose that drinkers will only drink certain beers at certain bars. Our three binary relationships Likes, Sells, and Frequents do not allow us to make this distinction. But a 3-way relationship would. name addr name Beers Bars Preferences manf license Drinkers name addr 27
Multiway Relationships Bar Joe s Bar Sue s Bar Sue s Bar Joe s Bar Joe s Bar Joe s Bar Sue s Bar Drinker Ann Ann Ann Bob Bob Cal Cal Beer Miller Bud Pete s Ale Bud Miller Miller Bud Lite 28
Multiplicity of Relationships We consider binary relationships, i.e., relationship between two entity sets. 29
Multiplicity of Relationships We consider binary relationships, i.e., relationship between two entity sets. In a many-many relationship, an entity of each set can be connected to many entities of the other set. 30
Multiplicity of Relationships We consider binary relationships, i.e., relationship between two entity sets. In a many-many relationship, an entity of each set can be connected to many entities of the other set. Example. a bar Sells many beers; a beer is sold by many bars. Beers Bars A many-many relationship Sells 31
Multiplicity of Relationships We consider binary relationships, i.e., relationship between two entity sets. In a many-one relationship, an entity in one A of the entity sets is connected to at most one entity of the other entity set B, but an entity of B can be connected any number of entities of A. 32
Multiplicity of Relationships We consider binary relationships, i.e., relationship between two entity sets. In a many-one relationship, an entity in one A of the entity sets is connected to at most one entity of the other entity set B, but an entity of B can be connected any number of entities of A. Example. Favorite, from Drinkers to Beers is many-one. Drinkers Beers A many-one relationship Favorite 33
Multiplicity of Relationships We consider binary relationships, i.e., relationship between two entity sets. In a one-one relationship, each entity of either entity set is related to at most one entity of the other set. 34
Multiplicity of Relationships We consider binary relationships, i.e., relationship between two entity sets. In a one-one relationship, each entity of either entity set is related to at most one entity of the other set. Example. Relationship Best-seller between entity sets Manfs (manufacturer) and Beers. Manfs Beers A one-one relationship Best-seller 35
Representing Multiplicity Show a many-one relationship by an arrow entering the one side. Drinkers Beers Likes Favorite 37
Representing Multiplicity Show a many-one relationship by an arrow entering the one side. Drinkers Beers Likes Favorite Show a one-one relationship by arrows entering both entity sets. 38
Representing Multiplicity Rounded arrow = exactly one, i.e., each entity of the first set is related to exactly one entity of the target set. 39
Representing Multiplicity Rounded arrow = exactly one, i.e., each entity of the first set is related to exactly one entity of the target set. Consider Best-seller between Manfs and Beers. Some beers are not the best-seller of any manufacturer, so a rounded arrow to Manfs would be inappropriate. But a beer manufacturer has to have a best-seller. Best- seller Manfs Beers 40
Attributes on Relationships Sometimes it is useful to attach an attribute to a relationship. Think of this attribute as a property of tuples in the relationship set. 41
Attributes on Relationships Sometimes it is useful to attach an attribute to a relationship. Think of this attribute as a property of tuples in the relationship set. Example. Price is a function of both the bar and the beer, not of one alone. Bars Beers Sells price 42
Attributes on Relationships Sometimes it is useful to attach an attribute to a relationship. Think of this attribute as a property of tuples in the relationship set. Example. Price is a function of both the bar and the beer, not of one alone. Bars Beers Sells price Equivalently, you can create an entity set representing values of the attribute, and make that entity set participate in the relationship. Bars Beers Sells Prices price 43
Attributes on Relationships Sometimes it is useful to attach an attribute to a relationship. Think of this attribute as a property of tuples in the relationship set. Example. Price is a function of both the bar and the beer, not of one alone. Bars Beers Sells price Equivalently, you can create an entity set representing values of the attribute, and make that entity set participate in the relationship. unique one of these. Bars Beers Sells Note convention: arrow from multiway relationship means all other entity sets together determine a Prices price 44
Roles Sometimes an entity set appears more than once in a relationship. 45
Roles Sometimes an entity set appears more than once in a relationship. Example 1. Married relationship Husband Wife Bob Joe Ann Sue Married Drinkers 46
Roles Sometimes an entity set appears more than once in a relationship. Example 1. Married relationship Example 2. Buddies relationship Buddy1 Buddy2 Bob Ann Joe Sue Ann Bob Joe Moe Husband Wife Bob Joe Ann Sue Married Buddies Drinkers Drinkers 47
Roles Sometimes an entity set appears more than once in a relationship. Example 1. Married relationship Example 2. Buddies relationship Label the edges between the relationship and the entity set with names called roles. Buddy1 Buddy2 Bob Ann Joe Sue Ann Bob Joe Moe Husband Wife Bob Joe Ann Sue Married Buddies Drinkers Drinkers 48
Roles Sometimes an entity set appears more than once in a relationship. Example 1. Married relationship Example 2. Buddies relationship Label the edges between the relationship and the entity set with names called roles. Buddy1 Buddy2 Bob Ann Joe Sue Ann Bob Joe Moe Husband Wife Bob Joe Ann Sue Married Buddies 2 husband wife 1 Drinkers Drinkers 49
