Database System Development Essentials
Explore the process of developing a database system, including entity-relationship diagrams, converting diagrams into tables, DML operations, user interface development, normalization, schema definition, and testing. Understand the Entity/Relationship Model and its properties in designing databases effectively.
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 2024 Instructor: Jianer Chen Office: HRBB 338C 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
The Entity/Relationship Model The E/R model is an intuitive (graphical) model used to sketch database designs, (where entity = object). 4
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. 5
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). 6
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. 7
Entity Sets Entity = thing or object. 8
Entity Sets Entity = thing or object. Entity set = collection of similar entities. Similar to a class in object-oriented languages. 9
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. 10
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. 11
E/R Diagrams In an entity-relationship diagram: 12
E/R Diagrams In an entity-relationship diagram: Entity set = rectangle. 13
E/R Diagrams In an entity-relationship diagram: Entity set = rectangle. Attribute = oval, with a line to the rectangle representing its entity set. 14
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) 15
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) 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. 18
Relationships A relationship connects some entity sets. It is given by a diamond, with lines to each of the entity sets involved. 19
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 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 license 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 name addr 22
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 23
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 24
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: 25
Multiway Relationships Sometimes, we need a relationship that connects more than two entity sets. 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. 27
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. 28
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. 29
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 30
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 31
Multiplicity of Relationships We consider binary relationships, i.e., relationship between two entity sets. 33
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. 34
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. 35
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 36
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. 37
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. 38
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 39
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. 40
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. 41
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 42
Representing Multiplicity Show a many-one relationship by an arrow entering the one side. 44
Representing Multiplicity Show a many-one relationship by an arrow entering the one side. Show a one-one relationship by arrows entering both entity sets. 45
Representing Multiplicity Show a many-one relationship by an arrow entering the one side. Show a one-one relationship by arrows entering both entity sets. Rounded arrow = exactly one, i.e., each entity of the first set is related to exactly one entity of the target set. 46
Representing Multiplicity Show a many-one relationship by an arrow entering the one side. Show a one-one relationship by arrows entering both entity sets. Rounded arrow = exactly one, i.e., each entity of the first set is related to exactly one entity of the target set. Drinkers Beers Likes Favorite 47
Example Consider Best-seller between Manfs and Beers. 48
Example 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 49
Attributes on Relationships Sometimes it is useful to attach an attribute to a relationship. 50
