Entity Relationship Diagram, Lecture notes of Database Management Systems (DBMS)

Download Entity Relationship Diagram and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity! Steps to Create an ERD Eg: In a university, a Student enrols in Courses. A student must be assigned to at least one or more Courses. Each course is taught by a single Professor. To maintain instruction quality, a Professor can deliver only one course Step 1) Entity Identification We have three entities  Student  Course  Professor Step 2) Relationship Identification We have the following two relationships  The student is assigned a course  Professor delivers a course Step 3) Cardinality Identification  A student can be assigned multiple courses  A Professor can deliver only one course 1 M N 1 Student Course Professor Student Course Professor Assigned Delivers Student Course Professor Assigned Delivers 2 Step 4) Identify Attributes Once, you have a list of Attributes, you need to map them to the identified entities. Ensure an attribute is to be paired with exactly one entity Once the mapping is done, identify the primary Keys. If a unique key is not readily available, create one. Step 5) Create the ERD Representation of ERD Diagram Method 1 Student Course Professor Assigned Delivers Student_ID StudentName Course_ID Employee_ID ProfessorName CourseName 5 Aggregation Represents relationship between a whole object and its component. Consider a ternary relationship Works_On between Employee, Branch and Manager. Now the best way to model this situation is to use aggregation, so, the relationship-set, Works_On is a higher level entity-set. Such an entity-set is treated in the same manner as any other entity-set. We can create a binary relationship, Manager, between Works_On and Manager to represent who manages what tasks. Logical schema of a database This can be defined as the design of the database at its logical level. In this level, the programmers, as well as the database administrator (DBA), work. At this level, data can be described as certain types of data records that can be stored in the form of data structures. However, the internal details (such as an implementation of data structure) will be remaining hidden at this level (tabular structure of representing ER diagram in order to visualize the entities, relationships and relationship keys in a more design oriented way). Database Schema A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data. A database schema defines its entities and the relationship among them. It contains a descriptive detail of the database, which can be depicted by means of schema diagrams. It’s the database designers who design the schema to help programmers understand the database and make it useful (design tabular method of representing a scenario with relations, relationships and keys). 6 A database schema can be divided broadly into two categories −  Physical Database Schema – This schema pertains to the actual storage of data and its form of storage like files, indices, etc. It defines how the data will be stored in a secondary storage.  Logical Database Schema – This schema defines all the logical constraints that need to be applied on the data stored. It defines tables, views, and integrity constraints. Relational schema Relational schema refers to the meta-data that describes the structure of data within a certain domain. It is the blueprint of a database that outlines the way its structure organizes data into tables. Relational instances (Database) It is important that we distinguish these two terms individually. Database schema is the skeleton of database. It is designed when the database doesn't exist at all. Once the database is operational, it is very difficult to make any changes to it. A database schema does not contain any data or information. A database instance is a state of operational database with data at any given time. It contains a snapshot of the database. Database instances tend to change with time. A DBMS ensures that its every instance (state) is in a valid state, by diligently following all the validations, constraints, and conditions that the database designers have imposed. Keys  Candidate key - Each table has only a single primary key. Each relation may have one or more candidate key. One of these candidate key is called Primary Key. Each candidate key qualifies for Primary Key. Therefore candidates for Primary Key is called Candidate Key. Candidate key can be a single column or combination of more than one column. A minimal super key is called a candidate key. Eg: EmployeeID and EmployeeEmail, both can be a Primary key; therefore both are candidate keys. Select any of the as Primary Key for your table, since a table can have only a single Primary Key. 7  Primary key - A primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records. A primary key is used as a unique identifier to quickly parse data within the database and find the relation between different tables. A relational database cannot have more than one primary key. A primary key’s main features are:  It must contain a unique value for each row of data.  It cannot contain null values.  Every row must have a primary key value. A primary key is either an existing table column or a column that is specifically generated by the database according to a defined sequence  Alternate key - Alternate Key or Secondary Key is the key that has not been selected to be the primary key, but are candidate keys. However, it is considered a candidate key for the primary key. A candidate key not selected as a primary key is called alternate or secondary key. Candidate key is an attribute or set of attributes that you can consider as a Primary key. Eg: selected Student_ID as primary key, therefore Student_Enroll and Student_Email will be Alternate Key  Foreign key - A Foreign Key creates a link between tables. It references the primary key in another table and links it. Eg: DeptID in the Employee table is a foreign key: