Only the individual part
PPT_Template_16_9_2017 Office | Faculty | Department1 | Week 8 Physical level database design DATA 200 Data and Information Management Office | Faculty | Department2 | Why are we doing this? • The third step of database design is physical-level database design. • It involves converting the logical design in to a physically implemented database using software. Office | Faculty | Department3 | Learning Objectives At the end of this chapter, you should be able to understand • Steps involved in physical database design methodology • Design approaches – Top down and Bottom up • Importance of avoiding one-to-one and many-to-many relationships Office | Faculty | Department4 | Overview • By now you have gone through the first two steps of database design process - conceptual and logical level database design. • Now you are ready to begin the physical-level design process by implementing the design for the specific DBMS selected by the organisation. • In this class you will use Access DBMS. • You will explore the general database design method and view examples illustrating this method. • The entity-relationship diagrams (ERD) developed to represent the database design visually can be used as a guide when implementing the physical database. Office | Faculty | Department5 | Logical vs Physical Database Design • The phase prior to physical design—logical database design—is largely independent of implementation details, such as the specific functionality of the target DBMS and application programs, but is dependent on the target data model. • The output of this process is a logical data model consisting of an ER/relation diagram, relational schema, and supporting documentation that describes this model, such as a data dictionary. • Together, these represent the sources of information for the physical design process and provide the physical database designer with a vehicle for making trade-offs that are so important to an efficient database design. • Whereas logical database design is concerned with the what, physical database design is concerned with the how Office | Faculty | Department6 | All the steps involved in Physical Database Design Methodology Step 1 Conceptual database design Step 2 Logical database design Step 3 Translate logical data model for target DBMS • Step 3.1 Design base relations • Step 3.2 Design representation of derived data • Step 3.3 Design general constraints Step 4 Design file organizations and indexes • Step 4.1 Analyse transactions • Step 4.2 Choose file organizations • Step 4.3 Choose indexes • Step 4.4 Estimate disk space requirements Step 5 Design user views Step 6 Design security mechanisms Step 7 Consider the introduction of controlled redundancy Step 8 Monitor and tune operational system Office | Faculty | Department7 | Steps involved in developing a physical database within the scope of DATA200 subject Steps: 1) Design the base tables • The objective is to decide how to represent base tables identified in logical model in target DBMS. • For each table, need to define: • name of the table; • field names and data types • PK and, where appropriate, AKs and FKs. 2) Create relationships between tables • referential integrity constraints for any FKs identified • resolve any one-to-one and many-to-many relationships to avoid issues 3) Normalisation • Perform normalisation up to 3NF 4) Create Queries 5) Enforce any restrictions/constraints in the code (Note: This cannot be shown in the logical design) Office | Faculty | Department8 | Design approaches: Top-Down Versus Bottom- Up Bottom-up design method • Design starts at low level • Specific user requirements drive design process Top-down design method • Begins with general database that models overall enterprise • Refines model until design supports all necessary applications 8 Office | Faculty | Department9 | Top down design Office | Faculty | Department10 | Bottom Up Design Office | Faculty | Department11 | One-to-One Relationship Considerations Simply include the primary key of each table as a foreign key in the other table • No guarantee that the information will match One solution: create a single table • Workable, but not the best solution Better solution • Create separate tables for customers and sales reps • Include the primary key of one of them as a foreign key in the other 1 1 Office | Faculty | Department12 | One-to-One Relationship Considerations (continued) One-to-one relationship implemented by including the primary key of one table as the foreign key (and alternate key) in the other table 12 Office | Faculty | Department13 | Many-to-Many Relationship Considerations • Complex issues arise when more than two entities are related in a many- to-many relationship • Many-to-many-to-many relationship: involves multiple entities • Deciding between a single many-to-many-to-many relationship and two (or three) many-to-many relationships • Crucial issue: independence 1 3 PPT_Template_16_9_2017 Office | Faculty | Department1 | Week 7 Logical level design and ER Modelling DATA 200 Data and Information Management Office | Faculty | Department2 | Why are we doing this? • The second step of database design is logical database design. • It involves arranging data into a series of logical relationships called entities and attributes. • An entity represents a chunk of information of a thing or a person. In relational databases, an entity often maps to a table. • An attribute is a component of an entity and helps define the uniqueness of the entity. • The entity-relationship model (or ER model) is a way of graphically representing the logical relationships of entities in order to create a database. Office | Faculty | Department3 | Learning Objectives At the end of this chapter, you should be able to understand • Logical Database Design • How to draw Entity- Relationship Diagrams (ERD) Office | Faculty | Department4 | Database Design • Logical Design • Entity design without any regard to a relational database management system (DBMS) • Physical Design • Logical design adapted to a particular DBMS • In this class it is Access DBMS software Office | Faculty | Department5 | Database Design Logical Design Physical Design Theoretical Entity Table Relation Attribute Column, Field Attribute Row, Record Tuple Office | Faculty | Department6 | Entity Relationship Diagram (ERD) • In order to show the logical design, one of the tools that is used is Entity Relationship (ER) Diagram. • It is a standard method to diagram entities, relationships and attributes • It can be described as a modelling technique. • Process of creating ER diagrams. • Very Important: Prior to creating ER diagram, we need to do some analysis to find out the requirements of the database. • What the new database should do? • What are the data required? • Analysis should reveal nouns (person, place or thing) Office | Faculty | Department7 | Entity Relationship Diagram (ERD) • Analysis should reveal nouns (person, place or thing) • Example: author, author name, book ISBN, book price, author contact, author email, book name, • Group nouns to create common themes (entities) • Author, Book • Other nouns that describe the common themes (attributes) • Author_name, author_contact, author_email, book_ISBN, book_price, book_name • Some of the nouns uniquely identify a specific instance of an entitiy (Primary Key) Office | Faculty | Department8 | How to show Entity Office | Faculty | Department9 | How to show Entity In some software tools there is a place to show the keys. Otherwise simply write (PK) or (FK) near the key. Office | Faculty | Department10 | Relationships This diagram shows correctly drawn two entities. As authors write books, these two entities have a relationship. Office | Faculty | Department11 | Relationships • As authors write books, these two entitles have a relationship. • There are different type of primary keys. • Natural key – something we do not make up • Surrogate key – something we make up. Office | Faculty | Department12 | Notations • There are different notations. • We will be using Crow’s foot notation. Office | Faculty | Department13 | Cardinality Degree to which different entities are related to. Office | Faculty | Department14 | Cardinality • Read from left to right. • A book has one and only one author. Business Rule: Of course, a book can have more than one author but in the example, business rule book has only one author. Office | Faculty | Department15 | Cardinality • Read from right to left • An author can write 1 or more books Office | Faculty | Department16 | Office | Faculty | Department17 | Office | Faculty | Department18 | • Cannot have many things on both sides. • You may get confused when trying to force this to a one-many relationship. • Most probably this is a many-many relationship. Office | Faculty | Department19 | PPT_Template_16_9_2017 Office | Faculty | Department1 | Week 6 Normalisation DATA 200 Data and Information Management Office | Faculty | Department2 | Why are we doing this? • How problems associated with the software development led to the software crisis. • How the software crisis led to a structured approach to software development called the information systems lifecycle. • About the relationship between the information systems lifecycle and the database system development lifecycle. Office | Faculty | Department3 | Why are we doing this? • When we design a database for an enterprise, the main objective is to create an accurate representation of the data, relationships between the data, and constraints on the data that is pertinent to the enterprise. • To help achieve this objective, we can use one or more database design techniques. • ER Modelling (Entity Relationship modelling) • Normalisation • In week 6, we will concentrate on Normalisation. • Normalisation is a database design technique that begins by examining the relationships (called functional dependencies) between attributes. Attributes describe some property of the data or of the relationships between the data that is important to the enterprise. • Normalisation uses a series of tests (described as normal forms) to help identify the optimal grouping for these attributes to ultimately identify a set of suitable relations that supports the data requirements of the enterprise. Office | Faculty | Department4 | Normalisation • Normalisation is a database design technique that begins by examining the relationships (called functional dependencies) between attributes. Attributes describe some property of the data or of the relationships between the data that is important to the enterprise. • Normalisation uses a series of tests (described as Normal forms) to help identify the optimal grouping for these attributes to ultimately identify a set of suitable relations that supports the data requirements of the enterprise. Office | Faculty | Department5 | Data redundancy and update anomalies Major aim of