DATABASE DESIGN DEVELOPMENT 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 • 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.
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
• 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
MAPPING ERD TO LOGICAL DESIGN (RELATIONAL) Entities - Tables Attributes - Fields Key attributes - Primary keys Relationship - _____ field (foreign key)
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
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).
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 = 'E 10001'
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 staff. No attribute, which is the primary key for the Staff entity type.
• 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.
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.
PRACTICAL • Go to the link below: https: //www. youtube. com/watch? v=PBhft. KTmd. HI&list=PL 4 Uez. Tf. GBADBm. COYt. Q 8 Qo hfl. QNY 1 y 3 o. E 7 • 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 : Open. Query – Practical 7: How to Make a Database - Part 7 - Visual Basic : Transfer Spreadsheet ac. Export
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. 6 th Ed. Global Edition. Pearson.
- Slides: 17