database design powerpoint presentation

Entity-Relationship Model

• A database can be modeled as:
• a collection of entities,
• relationship among entities.
• An entity is an object that exists and is distinguishable from other objects.
• Example: specific person, company, event, plant
• Entities have attributes
• Example: people have names and addresses
• An entity set is a set of entities of the same type that share the same properties.
• Example: set of all persons, companies, trees, holidays

Entity Sets instructor and student

instructor_ID instructor_name student-ID student_name

Relationship Sets

• A relationship is an association among several entities

Example:� 44553 (Peltier ) advisor 22222 ( Einstein) � student entity relationship set instructor entity

• A relationship set is a mathematical relation among n ≥ 2 entities, each taken from entity sets

{( e 1 , e 2 , … e n ) | e 1 ∈ E 1 , e 2 ∈ E 2 , …, e n ∈ E n }��where ( e 1 , e 2 , …, e n ) is a relationship

(44553,22222) ∈ advisor

Relationship Set advisor

• An attribute can also be property of a relationship set.
• For instance, the advisor relationship set between entity sets instructor and student may have the attribute date which tracks when the student started being associated with the advisor

Degree of a Relationship Set

• binary relationship
• involve two entity sets (or degree two).
• most relationship sets in a database system are binary.
• Relationships between more than two entity sets are rare. Most relationships are binary.
• Example: students work on research projects under the guidance of an instructor .
• relationship proj_guide is a ternary relationship between instructor, student, and project
• An entity is represented by a set of attributes, that is descriptive properties possessed by all members of an entity set.

instructor = ( ID, name, street, city, salary ) � course= ( course_id, title, credits )

• Domain – the set of permitted values for each attribute
• Attribute types:
• Simple and composite attributes.
• Single-valued and multivalued attributes
• Example: multivalued attribute: phone_numbers
• Derived attributes
• Can be computed from other attributes
• Example: age, given date_of_birth

Composite Attributes

Mapping Cardinality Constraints

• Express the number of entities to which another entity can be associated via a relationship set.
• Most useful in describing binary relationship sets.
• For a binary relationship set the mapping cardinality must be one of the following types:

One to many

Many to one

Many to many

Mapping Cardinalities

Note: Some elements in A and B may not be mapped to any

elements in the other set

• A super key of an entity set is a set of one or more attributes whose values uniquely determine each entity.
• A candidate key of an entity set is a minimal super key
• ID is candidate key of instructor
• course_id is candidate key of course
• Although several candidate keys may exist, one of the candidate keys is selected to be the primary key .

Keys for Relationship Sets

• The combination of primary keys of the participating entity sets forms a super key of a relationship set.
• ( s_id, i_id ) is the super key of advisor
• NOTE: this means a pair of entity sets can have at most one relationship in a particular relationship set.
• Example: if we wish to track multiple meeting dates between a student and her advisor, we cannot assume a relationship for each meeting. We can use a multivalued attribute though
• Must consider the mapping cardinality of the relationship set when deciding what are the candidate keys
• Need to consider semantics of relationship set in selecting the primary key in case of more than one candidate key

Redundant Attributes

• Suppose we have entity sets
• instructor , with attributes including dept_name

and a relationship

• inst_dept relating instructor and department
• Attribute dept_name in entity instructor is redundant since there is an explicit relationship inst_dept which relates instructors to departments
• The attribute replicates information present in the relationship, and should be removed from instructor
• BUT: when converting back to tables, in some cases the attribute gets reintroduced, as we will see.

E-R Diagrams

• Rectangles represent entity sets.
• Diamonds represent relationship sets.
• Attributes listed inside entity rectangle
• Underline indicates primary key attributes

Entity With Composite, Multivalued, and Derived Attributes

Relationship Sets with Attributes

Cardinality Constraints

• We express cardinality constraints by drawing either a directed line (→), signifying “one,” or an undirected line (—), signifying “many,” between the relationship set and the entity set.
• One-to-one relationship:
• A student is associated with at most one instructor via the relationship advisor
• A student is associated with at most one department via stud_dept

One-to-One Relationship

• one-to-one relationship between an instructor and a student
• an instructor is associated with at most one student via advisor
• and a student is associated with at most one instructor via advisor

One-to-Many Relationship

• one-to-many relationship between an instructor and a student
• an instructor is associated with several (including 0) students via advisor
• a student is associated with at most one instructor via advisor,

Many-to-One Relationships

• In a many-to-one relationship between an instructor and a student,
• an instructor is associated with at most one student via advisor ,
• and a student is associated with several (including 0) instructors via advisor

Many-to-Many Relationship

• An instructor is associated with several (possibly 0) students via advisor
• A student is associated with several (possibly 0) instructors via advisor

Participation of an Entity Set in a Relationship Set

• Total participation (indicated by double line): every entity in the entity set participates in at least one relationship in the relationship set
• E.g., participation of section in sec_course is total
• every section must have an associated course
• Partial participation: some entities may not participate in any relationship in the relationship set
• Example: participation of instructor in advisor is partial

E-R Diagram with a Ternary Relationship

Weak Entity Sets

• An entity set that does not have a primary key is referred to as a weak entity set .
• The existence of a weak entity set depends on the existence of a identifying entity set
• It must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set
• Identifying relationship depicted using a double diamond
• The discriminator ( or partial key) of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity set.
• The primary key of a weak entity set is formed by the primary key of the strong entity set on which the weak entity set is existence dependent, plus the weak entity set’s discriminator.
• We underline the discriminator of a weak entity set with a dashed line.
• We put the identifying relationship of a weak entity in a double diamond.
• Primary key for section – ( course_id, sec_id, semester, year )
• Note: the primary key of the strong entity set is not explicitly stored with the weak entity set, since it is implicit in the identifying relationship.
• If course_id were explicitly stored, section could be made a strong entity, but then the relationship between section and course would be duplicated by an implicit relationship defined by the attribute course_id common to course and section

E-R Diagram for a University Enterprise

Reduction to Relational Schemas

Reduction to Relation Schemas

• Entity sets and relationship sets can be expressed uniformly as relation schemas that represent the contents of the database.
• A database which conforms to an E-R diagram can be represented by a collection of schemas.
• For each entity set and relationship set there is a unique schema that is assigned the name of the corresponding entity set or relationship set.
• Each schema has a number of columns (generally corresponding to attributes), which have unique names.

Representing Entity Sets With Simple Attributes

• A strong entity set reduces to a schema with the same attributes� student( ID , name, tot_cred)
• A weak entity set becomes a table that includes a column for the primary key of the identifying strong entity set � section ( course_id, sec_id, sem, year )

Representing Relationship Sets

• A many-to-many relationship set is represented as a schema with attributes for the primary keys of the two participating entity sets, and any descriptive attributes of the relationship set.
• Example: schema for relationship set advisor

advisor = ( s_id, i_id )

Redundancy of Schemas

• Many-to-one and one-to-many relationship sets that are total on the many-side can be represented by adding an extra attribute to the “many” side, containing the primary key of the “one” side
• Example: Instead of creating a schema for relationship set inst_dept , add an attribute dept_name to the schema arising from entity set instructor
• For one-to-one relationship sets, either side can be chosen to act as the “many” side
• That is, extra attribute can be added to either of the tables corresponding to the two entity sets
• If participation is partial on the “many” side, replacing a schema by an extra attribute in the schema corresponding to the “many” side could result in null values
• The schema corresponding to a relationship set linking a weak entity set to its identifying strong entity set is redundant.
• Example: The section schema already contains the attributes that would appear in the sec_course schema

Composite and Multivalued Attributes

• Composite attributes are flattened out by creating a separate attribute for each component attribute
• Example: given entity set instructor with composite attribute name with component attributes first_name and last_name the schema corresponding to the entity set has two attributes name_first_name and name_last_name
• Prefix omitted if there is no ambiguity
• Ignoring multivalued attributes, extended instructor schema is
• instructor(ID, � first_name, middle_initial, last_name,� street_number, street_name, � apt_number, city, state, zip_code, � date_of_birth)
• A multivalued attribute M of an entity E is represented by a separate schema EM
• Schema EM has attributes corresponding to the primary key of E and an attribute corresponding to multivalued attribute M
• Example: Multivalued attribute phone_number of instructor is represented by a schema:� inst_phone= ( ID , phone_number )
• Each value of the multivalued attribute maps to a separate tuple of the relation on schema EM
• For example, an instructor entity with primary key 22222 and phone numbers 456-7890 and 123-4567 maps to two tuples: � (22222, 456-7890) and (22222, 123-4567)

Multivalued Attributes

• Special case:entity time_slot has only one attribute other than the primary-key attribute, and that attribute is multivalued
• Optimization: Don’t create the relation corresponding to the entity, just create the one corresponding to the multivalued attribute
• time_slot ( time_slot_id, day, start_time , end_time )
• Caveat: time_slot attribute of section ( from sec_time_slot ) cannot be a foreign key due to this optimization

Design Issues

• Use of entity sets vs. attributes � � ���
• Use of phone as an entity allows extra information about phone numbers (plus multiple phone numbers)
• Use of entity sets vs. relationship sets � Possible guideline is to designate a relationship set to describe an action that occurs between entities
• Binary versus n-ary relationship sets � Although it is possible to replace any nonbinary ( n -ary, for n > 2) relationship set by a number of distinct binary relationship sets, a n -ary relationship set shows more clearly that several entities participate in a single relationship.
• Placement of relationship attributes

e.g., attribute date as attribute of advisor or as attribute of student

Database Design Slide 1

This document provides an overview of key database concepts, including: - Types of databases and database management systems (DBMS) functions - Data models like relational, hierarchical, and object-oriented - The three-schema architecture with conceptual, internal, and external schemas - Languages used to define and manipulate database structures and data - Centralized and client-server database system architectures Read less

More Related Content

• 1. Database Systems Basic Concepts Dr. Ali H. El-Bastawissy Faculty of computers and Informatics Cairo University
• 2. Unit01 Outlines Types of Databases and Database Applications Basic Definitions Typical DBMS Functionality Examples of a Database Main Characteristics of the Database Approach Database Users Advantages of Using the Database Approach When Not to Use Databases
• 3. Definitions Data: known facts that can be recorded Database : a collection of data represents some aspect of the real world logically coherent collection (not a random collection) designed, built & populated for a specific purpose Database Management System : the software that manages the data
• 4. Database Systems Data Integrity, Data definition, DBMS Payroll Grades Schedules Data Definition
• 5. Characteristics of the Database Approach insulation of programs & data support of multiple user views use of a catalog to support database descriptions DB A collection of described and interrelated data stored and managed by general purpose DBMS
• 6. DBMS Functions Data Dictionary Data Storage Management Data Transformation and Presentation Security Multi-user Access Control Backup and Recovery Data Integrity Database Access Language Database Communication Interface
• 7. Database System Environment Application Programs/Queries Software: Query Processing & Programs Software: Data Access DBMS Software Database System Users/Programmers Database Definition Database
• 9. Database Players DBA access authorization, coordination & monitoring database usage, problem determination, performance tuning etc Designers identify the requirements & chose the appropriate structures to represent & store the data Users (Casual, parametric, Sophisticated, stand-alone) System analysts & application programmers DBMS system designers & implementers Tool developers Operators & maintenance personnel
• 10. DBMS Interfaces Menu-based Interfaces for browsing Forms-based Interfaces GUIs Natural Language Interfaces Specialized Interfaces Interfaces for the DBA Query Language
• 11. Classification of DBMSs Data Model Classification relational, network, hierarchical, object-oriented Number of users single user or multi-user Number of Sites centralized vs distributed Cost of the DBMS
• 12. Areas of Database Study DBMS software Database Design Query Languages Application Programming Database Administration Data Warehousing & Data Mining
• 13. Costs of using a DBMS: High initial investment and possible need for additional hardware. Overhead for providing generality, security, concurrency control, recovery, and integrity functions. When a DBMS may be unnecessary: If the database and applications are simple, well defined, and not expected to change. If there are stringent real-time requirements that may not be met because of DBMS overhead. If access to data by multiple users is not required. When not to use a DBMS
• 14. When no DBMS may suffice: If the database system is not able to handle the complexity of data because of modeling limitations If the database users need special operations not supported by the DBMS. When not to use a DBMS
• 15. Data Model A collection of concepts that can be used to define the DB structure (data items& types, relationships, operations|behavior, and constraints). Some Data Models: Entity Relationship model Relational Model hierarchical & network models object-data models
• 16. Physical Models Categories of Data Models Conceptual Models Entities, attributes & relationships Implementation Models Record Structures Record formats, record orderings, access paths
• 17. Conceptual (high-level, semantic) data models: Provide concepts that are close to the way many users perceive data. (Also called entity-based or object-based data models.) Physical (low-level, internal) data models: Provide concepts that describe details of how data is stored in the computer. These are usually specified in an ad-hoc manner through DBMS design and administration manuals Implementation (representational) data models: Provide concepts that fall between the above two, used by many commercial DBMS implementations (e.g. relational data models used in many commercial systems). Categories of Data Models
• 18. Three-Schema Architecture External View #1 External View #2 External View #n Conceptual Schema Internal Schema mapping mapping Stored database ...
• 19. Defines DBMS schemas at three levels: Internal schema at the internal level to describe physical storage structures and access paths (e.g indexes). Typically uses a physical data model. Conceptual schema at the conceptual level to describe the structure and constraints for the whole database for a community of users. Uses a conceptual or an implementation data model. External schemas at the external level to describe the various user views. Usually uses the same data model as the conceptual schema. Three-Schema Architecture
• 20. Three-Schema Architecture Mappings among schema levels are needed to transform requests and data. Programs refer to an external schema, and are mapped by the DBMS to the internal schema for execution. Data extracted from the internal DBMS level is reformatted to match the user’s external view (e.g. formatting the results of an SQL query for display in a Web page)
• 21. Data Independence Logical data independence - ability to change the conceptual schema without having to change the external schemas or application programs Physical data independence - ability to change the internal schema without having to change the conceptual (or external) schemas.
• 22. DBMS Languages DDL: D ata D efinition L anguage used to define/change the structure of the database (SDDL, CDDL, VDDL) DML: D ata M anipulation L anguage used to query the database, insert data, change data or delete data (Nonprocedural|procedural (embedded) DML) (set-at-a-time |record-at-a-time DML)
• 23. DBMS Architectures Centralized DBMS: Combines everything into single system including- DBMS software, hardware, application programs, and user interface processing software. User can still connect through a remote terminal – however, all processing is done at centralized site.
• 24. DBMS Architectures Basic 2-tier Client-Server Architectures Specialized Servers with Specialized functions Print server File server DBMS server Web server Email server Clients can access the specialized servers as needed
• 25. Logical two-tier client server architecture
• 26. Clients Provide appropriate interfaces through a client software module to access and utilize the various server resources. Clients may be diskless machines or PCs or Workstations with disks with only the client software installed. Connected to the servers via some form of a network. (LAN: local area network, wireless network, etc.)
• 27. DBMS Server Provides database query and transaction services to the clients Relational DBMS servers are often called SQL servers, query servers, or transaction servers Applications running on clients utilize an Application Program Interface ( API ) to access server databases via standard interface such as: ODBC: Open Database Connectivity standard JDBC: for Java programming access Client and server must install appropriate client module and server module software for ODBC or JDBC
• 28. DBMS Architectures Three Tier Client-Server Architecture Common for Web applications Intermediate Layer called Application Server or Web Server: Stores the web connectivity software and the business logic part of the application used to access the corresponding data from the database server Acts like a conduit for sending partially processed data between the database server and the client. Three-tier Architecture Can Enhance Security: Database server only accessible via middle tier Clients cannot directly access database server
• 30. Summary Types of Databases and Database Applications Basic Definitions Typical DBMS Functionality Examples of a Database Main Characteristics of the Database Approach Database Users Advantages of Using the Database Approach When Not to Use Databases
• 31. Summary Data Models and Their Categories History of Data Models Schemas, Instances, and States Three-Schema Architecture Data Independence DBMS Languages and Interfaces Classification of DBMSs Database System Utilities and Tools Centralized and Client-Server Architectures
• 32. Questions Dr. Ali H. El-Bastawissy Faculty of computers and Informatics Cairo University DB Unit 01 Intro Finish Line

• My presentations

Auth with social network:

Download presentation

We think you have liked this presentation. If you wish to download it, please recommend it to your friends in any social system. Share buttons are a little bit lower. Thank you!

Presentation is loading. Please wait.

Chapter 12: Designing Databases

Published by Gavin Andrews Modified over 9 years ago

Similar presentations

Presentation on theme: "Chapter 12: Designing Databases"— Presentation transcript:

Chapter 10: Designing Databases

C6 Databases.

BUSINESS DRIVEN TECHNOLOGY Plug-In T4 Designing Database Applications.

Introduction to Databases

Management Information Systems, Sixth Edition

File Systems and Databases

Database Design Conceptual –identify important entities and relationships –determine attribute domains and candidate keys –draw the E-R diagram Logical.

CS 432 Object-Oriented Analysis and Design

Data Management Design

Organizing Data & Information

1 © Prentice Hall, 2002 Chapter 5: Logical Database Design and the Relational Model Modern Database Management 6 th Edition Jeffrey A. Hoffer, Mary B.

Chapter 4: Database Management. Databases Before the Use of Computers Data kept in books, ledgers, card files, folders, and file cabinets Long response.

Chapter 4 Relational Databases Copyright © 2012 Pearson Education, Inc. publishing as Prentice Hall 4-1.

Managing Data Resources. File Organization Terms and Concepts Bit: Smallest unit of data; binary digit (0,1) Byte: Group of bits that represents a single.

Chapter 4 Relational Databases Copyright © 2012 Pearson Education 4-1.

Michael F. Price College of Business Chapter 6: Logical database design and the relational model.

IST Databases and DBMSs Todd S. Bacastow January 2005.

INTRODUCTION TO DATABASE USING MS ACCESS 2013 PART 2 NOVEMBER 4, 2014.

PHASE 3: SYSTEMS DESIGN Chapter 7 Data Design.

About project

© 2024 SlidePlayer.com Inc. All rights reserved.

• Preferences

Database Design - PowerPoint PPT Presentation

Something went wrong! Please try again and reload the page.

Database Design

Are persons, objects or events about which information is, or will be, recorded in the database ... normalization is the process of organizing data in a database. ... – powerpoint ppt presentation.

• The process of developing database structures from user requirements for data
• a structured methodology
• Structured Methodology - a number of ordered formal
• processes with known inputs and expected outputs
• 1. derive relationships
• 2. evolve to meet user requirements
• 3. Do it right the first time!
• Reduce data redundancy.
• Provide stable data structures that can be readily changed with changing user requirements.
• Allow users to make ad hoc requests for data.
• Maintain complex relationships between data elements.
• Support a large variety of decision needs
• data availability
• data reliability
• data currency
• data consistency
• data flexibility
• data efficiency
• Verify these criteria are satisfied via technical review
• 3 types of data objects
• Relationships
• ENTITIES Are persons, places, or things about which data is to be, or is, gathered
• ATTRIBUTES Are the properties of entities.Examples are Names, Tax Numbers, Age, Status
• RELATIONSHIPS Describe how entities relate to each other eg Customers BUY Products
• Persons WORK_ON Jobs
• Are persons, objects or events about which information is, or will be, recorded in the Database
• The designation of a thing about which data is to be collected, stored or processed.
• Many of these Entities can be identified with Business Activities (e.g. suppliers, purchase orders, customer)
• 2 entities can be related as
• One to One (1 1)
• The interpretation of this is that an occurrence of an entity A can relate to ONE and ONLY ONE occurrence of entity B, and an occurrence of entity B can relate to ONE and ONLY ONE occurrence of entity A
• One to Many (1 N)
• The interpretation is that ONE occurrence of entity A can relate to ONE or MORE occurrences of entity B, but an occurrence of entity B can relate to ONE ONLY occurrence of entity A
• Many to Many (N N) - a common business relationship
• The interpretation is that ONE or MORE occurrences of entity A can relate to ONE or MORE occurrences of entity B and ONE or MORE occurrences of entity B can relate to ONE or MORE occurrences of entity A
• Type Shown As Example
• One to One 11 Book -----gt Title
• One to Many 1N Publisher ----gtBooks
• Many to Many NN Books lt---gtAuthors
• Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating two factors redundancy and inconsistent dependency.
• Student Advisor Adv-Room Class1 Class2 Class3
• ------------------------------------------- ------------
• 1022 Jones 412 101-07 143-01 159-02
• 4123 Smith 216 201-01 211-02 214-01
• Eliminate repeating groups in individual tables.
• Create a separate table for each set of related data.
• Identify each set of related data with a primary key.
• Student Advisor Adv-Room Class
• ----------------------------------- ----
• 1022 Jones 412 101-07
• 1022 Jones 412 143-01
• 1022 Jones 412 159-02
• 4123 Smith 216 201-01
• 4123 Smith 216 211-02
• 4123 Smith 216 214-01
• Create separate tables for sets of values that apply to multiple records.
• Relate these tables with a foreign key.
• The following two tables demonstrate second normal form
• Students Student Advisor Adv-Room
• ------------------------- -----
• 1022 Jones 412
• 4123 Smith 216
• Registration Student Class
• ------------------
• 1022 101-07
• 1022 143-01
• 1022 159-02
• 4123 201-01
• 4123 211-02
• 4123 214-01
• Eliminate fields that do not depend on the key.
• Students Student Advisor
• -------------------
• Faculty Name Room Dept
• --------------------
• Jones 412 42
• Smith 216 42
• A database which will
• Accurately reflect the real world data in all
• required aspects
• Be responsive to Management information demands
• Reflect Business Rules and Controls
• Be capable of modification to meet changes
• in Management needs
• Be an asset to the Organization/Enterprise

PowerShow.com is a leading presentation sharing website. It has millions of presentations already uploaded and available with 1,000s more being uploaded by its users every day. Whatever your area of interest, here you’ll be able to find and view presentations you’ll love and possibly download. And, best of all, it is completely free and easy to use.

You might even have a presentation you’d like to share with others. If so, just upload it to PowerShow.com. We’ll convert it to an HTML5 slideshow that includes all the media types you’ve already added: audio, video, music, pictures, animations and transition effects. Then you can share it with your target audience as well as PowerShow.com’s millions of monthly visitors. And, again, it’s all free.

About the Developers

PowerShow.com is brought to you by  CrystalGraphics , the award-winning developer and market-leading publisher of rich-media enhancement products for presentations. Our product offerings include millions of PowerPoint templates, diagrams, animated 3D characters and more.