4.0 LOGICAL DESIGN

4.0 LOGICAL DESIGN

1. 4.1 Concept of Relational Model

   • The Entity Relational Model(ERM)
     • The Entity Relational Model (ERM) forms the basis of an ERD
     • The ERD represents the conceptual database as viewed by the end view
     • Four main components of ERD
       • Relationship
       • Keys
       • Attribute
       • Entity
     • Relational Model
       • Definition
         • A relational database is based on a logical structure where data is organized into tables (rows and columns)
       • In the relational model
         • Entities are mapped into tables
         • Entity occurences correspond to table rows
         • Attributes are the equivalent of fields in the file systems
         • The entity identifier is mapped as the table's primary key (PK)
       • Key attributes are also written as <PK> in a frequently used shorthand notation the table structure called a relational schema, that uses the following format
       • Example of relartional schema
         • STUDENT(Stud_ID<PK>, StudName,GenderID<FK>)
         • COURSE(CourseCode<PK>, CourseName, CreditHour,DeptID<FK>)
         • ENROLLMENT(StudID<PK><FK>, CourseCode <PK><FK>, Grade)
       • The components of a relational model
         • Tables/Relation
           • A table is perceived as a two-dimensional structure composed of rows and columns
           • A table is also called a relation because the relational model's creator, E.F. Codd used the two terms as synonyms
           • A table(relation) in a relational database is a structured format that organizes data into rows (tuples) and columns (attributes)
           • Each table represents an entity, set, meaning it holds information about a specific category of objects
         • Fields/Attributes/Column
           • A column in a table that represents a specific property of an entity
           • A single type data within the table
           • Each column has a name and a data type (e.g., integer, string, date)
         • Records/Tuples/Rows
           • A single row in a table, representing one instance of an entity
           • Each row contains data for each column in the table
           • Rows are also tuples in relational database terminology
         • Keys/Identifier/PK
           • One or more attributes that determine other attributes
           • Used to uniquely records and establish relationships between tables
           • Keys are important because they are used to ensure that each row in a table is uniquely identifiable. They are also used to establish relationships among tables and to ensure the integrity of the data
           • Type of Keys
             • Primary Key(PK)
               • Definition
                 • An attribute or combination of attributes that uniquely identifies any given row
               • Example
                 • StuNum(321452,324257,etc.)
             • Foreign Keys(FK)
               • Definition
                 • A primary key from one table that is placed into another table to create a common attribute
               • Example
                 • DeptCode references DEPARTMENT, ProfNum references, PROFESSOR
             • Superkey
               • Definition
                 • A set of attributes that uniquelyidentifies a row
               • Example
                 • (StuNum, StuPhone)
             • Candidate Key
               • Definition
                 • A minimal superkey that is a superkey without any unnecessary attributes
               • Example
                 • StuNum(Because it uniquely identifies students)
             • Secondary Key
               • Definition
                 • A key used strictly for data retrieval purposes
               • Example
                 • StuPhone
             • Composite Key
               • Definition
                 • Consists of two or more attributes that together uniquely identify a record
               • Example
                 • StuNum, ProfNum

2. 4.3 Data Dictionary

   • Data Dictionary
     • Definition
       • A DBMS components that stores metadata data about data, that describes the properties and context of user data
     • The data dictionary contains the data definition as well as their characteristics relationships
     • Also known as information resource dictionary
     • The purpose of data dictionary is to ensure that all members of database design and implementation teams use the same table and attribute names and characteristics
     • The use of data dictionary
       • The data dictionary can be a tool for monitoring database security by checking the assignment of data access privilege
       • The data dictionary to support data analysis and design
       • The data dictionary can also be used to ensure that application programmers have met the naming standards for data elements in the database, and that the data validation rules are correct
       • The data dictionary can be used to support a wide range of data administration activities
       • Facilitate the design and implementation of information systems
   • System Catalog
     • Definition
       • System Catalog is detailed system data dictionary that describes all objects in a database
     • Data
       • Table names
       • Field name in each table
       • Data type
       • Field length corresponding to each column
       • Constraints