23/10/20171 Lecture 5 Be able to design and implement relational databasesystems Designs: data types; entity and referential constraints;conversion of logical database design to a physicalimplementation; tools and techniques; issues around the degreeof normalisation chosen; verification and validity checks; datadefinition; control mechanisms Requirements: requirements specification; relationalrequirements; other requirements eg. need to integrate withlegacy systems, future requirements, … Continue reading “Database System Development Lifecycle | My Assignment Tutor”
23/10/20171 Lecture 5 Be able to design and implement relational databasesystems Designs: data types; entity and referential constraints;conversion of logical database design to a physicalimplementation; tools and techniques; issues around the degreeof normalisation chosen; verification and validity checks; datadefinition; control mechanisms Requirements: requirements specification; relationalrequirements; other requirements eg. need to integrate withlegacy systems, future requirements, timescales, costs. User interface: requirements eg functionality, reliability,consistency, performance, menu driven, HCI interface 23/10/20172 Database System Development Lifecycle Problem/Need Identified Database solution planning System definition Requirements collection and analysis Design Implementation DBMS select/develop Building a Database System – 3 Phases Requirements phase: Process of identifying data available andInformation required by users– a data model is developed– Data model is a logical representation of the database structure Design phase: It is the process of modelling real world in order to createa logical (conceptual) schema of a database.– the data model is transformed into tables and relationships( Forrelational databases only) Implementation phase: It is a process of mapping the logical model(schema) of a database into database tables.– Tables, relationships, and constraints are created– Stored procedures and triggers are written– The database is filled and systems are tested Database and its applications will be modified (through these same threephases) to meet new requirements 23/10/20173 Database Design Database Design from existing data.– First type of database design involves databasesthat are constructed from existing data.– In some cases, a design team is given a set ofspreadsheets or text files with data in them.– The design team is expected to design a newdatabase and import data from those spreadsheetsor files into the new database.– Alternatively, databases can be created fromextracts of other databases. 23/10/20176 Database design from new systems development.– A second way that databases are designed is from thedevelopment of new systems.– Requirements for a new system, such as desired dataentry forms and reports, user requirement statements,use cases and any other requirements are analysed tocreate a database design.– The design team proceeds in two steps:-» creation of a`data model from the requirement statements.» and transforming that data model into a database design.Database Design 23/10/20174 23/10/20177 Database design from redesign.– The third way that databases are designed.» Two common types of database redesign.» In the first, a database is adapted to new or changing requirements.This process is sometimes called database migration.» In the migration process, tables may be created, modified, removed;relationships may be altered; or data constraints may be modified.» The second type of database redesign involves the integration oftwo or more databases.» This type is common when adapting or removing legacy (‘old’)systems.Database Design 23/10/20178Example: Data Model 23/10/20175 23/10/20179 Application development proceeds in parallel withdatabase development.Application Development The design Process Identify Entity List Attributes Normalisation Establish Relationship types DDL DML + DBMS 23/10/20176 11 How does the theory we learned LO1correspond to the implementation:– Normalisation process/ Table creations.– Primary, Composite, and Foreign keys.– E-R Diagram– Referential integrity» Delete» UpdateBack to the lecture: Content Conceptual Design From logical to physical design Physical Design Completing the Design10/23/201712Structured HCI(Human Computer Interaction) Design 23/10/20177 Conceptual design Initially involves analysing users’ needs in terms of thetasks to be accomplished using a system and theobjects & operations a user has to employ toaccomplish the tasks. Conceptual design example : Eurochange– A design of a machine that exchanges money in oneEuropean currency for another – point-of-cashmachine.10/23/201713 Physical Design Concerns embedding the conceptual model of a systemin a physical structure so that users can communicatewith that system. Once the allocation of tasks to human and to machinehas been considered, the details of the operationalaspects of the system can be specified and developed. Operational aspects concern what the system doesphysically.10/23/201714 23/10/20178 Completing the design A structured or layered approach to design. The conceptual layer is used to determine the logical structure andfunctioning of the system. Then these are mapped onto a physical design. The method employs a variety of techniques as appropriate.10/23/201715 16The Relational Data Model, andMapping Strategies for ER/EERinto Relations 23/10/20179 17 A Relation – is a named two dimensional table ofdata. A relation has the following properties:– Every relation has a unique name.– Every attribute value is atomic.– Every row is unique.– Attributes in tables have unique names.– The order of the columns is irrelevant.– The order of the rows is irrelevant.Relational Definitions – Revisited 18 Primary Key Candidate Key Composite Key Foreign Key– One-to-Many Relationship– Many-to-Many Relationship» Intersection DataRelational Keys and Structures 23/10/201710 Relational Concepts Relational Algebra– a theoretical language with operations that work on one or morerelations to define another relation without changing the originalrelations Relational Calculus– Has no description of how to evaluate a query; the query onlyspecifies WHAT is to retrieved rather than HOW to retrieve it. Relational Operations– SELECT, PROJECT, JOIN» Equijoin – Join field appears twice.» Natural Join – Join field appears once. 20Schema with four Relations 23/10/201711 21 Domain Constraints– Allowable values for an attribute. Entity Integrity– No primary key attribute may be null. Operational Constraints– Business rules.Integrity Constraints 22Fig. 2.3: Referential integrity constraints 23/10/201712 Integrity Constraints Referential Integrity– Is a rule that maintains consistency among therows of two relations by ensuring that eithereach foreign key value must match a primarykey value in the other relation or the foreignkey value must be null– For example: Delete Rules» Restrict» Cascade» Set-to-Null Well-Structured Relations Insertion Anomaly Deletion Anomaly Modification Anomaly 23/10/201713 Transforming E-R Diagrams into Relations1. Map Regular Entities to Relations– Composite attributes: Use only their simple, componentattributes– Multi-valued Attribute – Becomes a separate relation with aforeign key taken from the superior entity (a) CUSTOMER entity type with composite attributeMapping a composite attribute 23/10/201714 CUSTOMER relation with address detail Transforming E-R Diagrams Into Relations2. Map Weak Entities– Becomes a separate relation with a foreign key taken from thesuperior entity (Fig. 1.11 in previous lecture). 23/10/201715 Example of mapping a weak entity(a) Weak entity DEPENDENT 30Fig. 2.6: (b) Relations resulting from weak entity 23/10/201716 Transforming E-R Diagrams Into Relations3. Map Binary Relationships– One-to-Many – Primary key on the one sidebecomes a foreign key on the many side (Fig.1.12 in previous lecture).– Many-to-Many – Create a new relation withthe primary keys of the two entities as itsprimary key (Fig. 1.13 in previous lecture).– One-to-One – Primary key on the mandatoryside becomes a foreign key on the optional side(Fig. 1.14 in previous lecture). Example of mapping a 1:M relationship(a) Relationship between customers and orders 23/10/201717 Fig. 2.7 (b) Mapping the relationship Example of mapping an M:N relationship(a) Requests relationship (M:N) 23/10/201718 35Fig. 2.8(b) Three resulting relations Fig. 2.9: Mapping a binary 1:1 relationship(a) Binary 1:1 relationship 23/10/201719 Fig. 2.9 (b) Resulting relations Transforming E-R Diagrams Into Relations4. Map Associative Entities– Identifier Not Assigned» Default primary key for the association relation is the primary keys of the two entities(Fig. 1.15 in previous lecture).– Identifier Assigned» It is natural and familiar to end-users.» Default identifier may not be unique.» (Fig. 1.16 in previous lecture). 23/10/201720 Mapping an associative entitywith an identifier(a) Associative entity (SHIPMENT) Fig. 2.10 (b) Three relations 23/10/201721 41Fig. 2.11: Mapping a ternary relationship(a) Ternary relationship with associative entity 42Fig. 2.11 (b) Mapping the ternary relationship 23/10/201722 43Fig. 2.12: Supertype/subtype relationships 44Fig. 2.13: Mapping Supertype/subtyperelationships to relations
