Week 1 Entity Relationship Diagram (1)

.pptx

School

Nanyang Technological University**We aren't endorsed by this school

Course

CSC SC2207

Subject

Information Systems

Date

Dec 17, 2024

Pages

Uploaded by MateRhinocerosMaster985

Entity Relationship Diagram (1)Week 1

Content-Database and DBMS-ER diagram-Types of relationships-Roles

Database and DBMSWhat is a database?-A collection of data specially organized for efficient retrieval by a computer What is a database system?-A piece of software that helps us efficiently manage/retrieve information from databasesMore formal name: Database Management System (DBMS)

DBMS in PracticeLarge web sites rely heavily on DBMS-Facebook-TwitterMany non-web companies, too-Banks, hospitals, etcEven small pieces of software on your computer -Google Chrome

Relational Model Numerous DBMS exist on the market-Oracle, SQL Server, MongoDB, …Most of them follow the relational modelWhat does it mean?Answer: They store all data in the form of relations.

RelationSome jargons:-A relation is often referred to as a table-A row in a table is also called a tuple or a record-A column in a table is also called an attribute of the table

Database and DBMS (cont.)A real database may have a large number of tables …Imagine that you are ask to design a database like this ….How would you approach this task?

Designing a Database for an ApplicationConceptually model the data requirements of the application -What are the things that need to be stored?-How do they interact with one another?Tool to use: Entity-Relationship (ER) Diagrams -A pictorial and intuitive way for modelling Translate the conceptual model into a set of tablesConstruct the tables with a DBMS

ER DiagramER diagram is a collection of visual artifactsEach artifact captures some data requirement or relationship

ER Diagram (cont.)Rectangle = Entity SetEntity = Real-world object (entity)Entity Set = Collection of similar objects (entities)Analogue: An object class in object-oriented programming language

ER Diagram (cont.)Oval = Attribute = Property of an entity setDiamond = Relationship = Connection between two entity sets-Companies make products

Types of Relationships-Many-to-Many Relationships-Many-to-One Relationships-One-to-One Relationships

Many-to-Many RelationshipOne person can buy multiple productsOne product can be bought by multiple personsNote: Some Person entity is not related to Product entities, vice versa

Many-to-One RelationshipOne company can make multiple productsBut one product can only be made by one companyNote: Some Company entity does not make any Product entity

One-to-One RelationshipA city can be the capital of only one countryA country can have only one capital cityNote: Some Country entity has no capital city, vice versa

Multi-way RelationshipsWhat if we want to record the store from which the person bought the product?We can use a 3-way relationshipDrawback: The arrows would be complicated

Multi-way Relationships (cont.)Should we use this? What does it mean?One <person, store> pair to one productOne product to many <person, store> pairsMeaning: A person only buys one product from one shop

Multi-way Relationships (cont.)What about this?<person, store> to product? many to one?<person, product> to store? many to one?Getting more complicated - avoid this

From Multi-way to Binary

ExampleEach student is mentored by one faculty memberEach faculty member can mentor multiple students

ExerciseEach player prefers only one game, but not vice versaMany-to-many? ✖Many-to-one? ✓One-to-one? ✖

Exercise (cont.)Any two players are from exactly two different countriesMany-to-many? ✖Many-to-one? ✖One-to-one? ✓

Exercise (cont.)No two shops sell the same productMany-to-many? ✖Many-to-one? ✓One-to-one? ✖

RolesSometimes an entity set may appear more than once in a relationshipExample: some persons are married to each otherThe role of the person is specified on the edge connecting the entity set to the relationship

Roles (cont.)Example: some employee supervises other employeesWithout the roles, it is unclear whether it is many-to-one from supervisees to supervisors, or the other way around

One More Thing about Relationships A relationship can have its own attributeA relationship can have its own attributeIf we want to record the date of the purchase

ExerciseConsider two entity sets, Shops and CompaniesEach shop sells products from at least one companyEach company has its product sold in at least one shopA shop may be the flagship shop of at most one companyEach company has at least one flagship shopsDraw some relationships between Shops and Companies to capture the above statements

Exercise (cont.)There can be multiple relationships between two entity sets