1 / 17

logical design for relational database

DATABASE DESIGN & DEVELOPMENT. logical design for relational database. Zatil Ridh'wah Hj Darot. Logical data modeling Motivation for logical data modeling Mapping ERD to logical design Data elements Entity relationship modeling Referential integrity. Logical design. Logical data modeling.

azize
Download Presentation

logical design for relational database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. DATABASE DESIGN & DEVELOPMENT logical design for relational database Zatil Ridh'wah Hj Darot

  2. Logical data modeling • Motivation for logical data modeling • Mapping ERD to logical design • Data elements • Entity relationship modeling • Referential integrity

  3. Logical design

  4. Logical data modeling • Logical data modeling is the process of representing __________________and organization in a graphical way without any regard to the physical implementation or the database management system technology involved in storing the data. • A logical data model provides all the information about the various entities and the relationships between the entities present in a database.

  5. motivation for logical data modeling Completion of conceptual modeling phase: • Results in reasonably clear picture of data requirements for application system at high level of abstraction. • Conceptual data model is technology- independent: • analysis and design activity is not constrained by boundaries of anticipated technology that will be used for implementation • _________________ may contain constructs not directly compatible with technology intended for implementation

  6. Future ____________ may be required to eliminate data redundancy in design • Transforming conceptual schema to state better compatible with implementation technology of choice is achieved via logical data modeling • Logical data modeling phase serves as transition from technology- independent conceptual schema to technology- dependent design

  7. Mapping erd to logical design (relational) • Entities - Tables • Attributes - Fields • Key attributes - Primary keys • Relationship - _________ field (foreign key)

  8. Data elements Table • A relational database consists of a collection of tables (i.e. entities), from which we want to seek information. • A table consists of columns, which are the properties of the table, and rows which are the records to store and retrieve. Table characteristics: • Each table represent a single subject • No data item will be unnecessarily stored in more than one table • All ______________ attributes in a table are dependent on the primary key • Each table is void of insertion, update, and deletion anomalies

  9. Column/ fields Columns refer to a set of fields in tables. A column describes a property we are interested in storing for the table it belongs to. Examples of fields are name, employee number, address etc. Attribute A table consists of several records(row), each record can be broken down into several smaller parts of data known as Attributes. Data types Commonly data types include: integers, floating- point numbers, string (or text), date/ time, binary, collection (such as enumeration and set).

  10. Data indexes • An index is defined by a field expression that you specify when you create the index. Typically, the field expression is a single field name, like EMP_ID. An index created on the EMP_ID field, for example, contains a sorted list of the employee ID values in the table. Each value in the list is accompanied by references to the records that contain that value. • A database driver can use indexes to find records quickly. An index on the EMP_ID field, for example, greatly reduces the time that the driver spends searching for a particular employee ID value. Consider the following Where clause: WHERE emp_id = 'E10001'

  11. entity relationship modeling Strong and Weak Entity Types • An entity type is referred to as being strong if its existence does not depend upon the existence of another entity type. • A ______________ of a strong entity type is that each entity occurrence is uniquely identifiable using the primary key attribute(s) of that entity type. • For example, we can uniquely identify each member of staff using the staffNo attribute, which is the primary key for the Staff entity type.

  12. A weak entity type is dependent on the existence of another entity type. An example of a weak entity type called Preference. • A characteristic of a weak entity is that each entity occurrence cannot be uniquely identified using only the attributes associated with that entity type. • Weak entity types are sometimes referred to as __________, dependent, or subordinate entities and strong entity types as parent, owner, or dominant entities.

  13. referential integrity • A foreign key links each tuple in the child relation to the tuple in the parent relation containing the matching candidate key value. • Referential integrity means that if the foreign key contains a value, that value must refer to an existing tuple in the parent relation.

  14. practical • Go to the link below: https://www.youtube.com/watch?v=PBhftKTmdHI&list=PL4UezTfGBADBmCOYtQ8QohflQNY1y3oE7 • Open Microsoft Access and do: • Practical 4: How to Make a Database - Part 4 - Queries • Practical 5: How to Make a Database - Part 5- Exporting Data • Practical 6: How to Make A Database - Part 6 - Visual Basic : OpenQuery • Practical 7: How to Make a Database - Part 7 - Visual Basic : Transfer Spreadsheet acExport

  15. references • Database Principles: Fundamentals of Design, Implementation, and Management. Tenth Edition. • Conolly, T. and Begg, C. (2014) Database Systems: A Practical Approach to Design, Implementation and Management. 6th Ed. Global Edition. Pearson.

More Related