Lecture 6 ER Keys Lecture PDF

Database INSTRUCTOR: AYSHA SAFDAR Entity and Entity Type 2. Entity Type 1. Entity An entity type is the definition or schema for a specific kind of An entity is anything in the real world t...

Database INSTRUCTOR: AYSHA SAFDAR Entity and Entity Type 2. Entity Type 1. Entity An entity type is the definition or schema for a specific kind of An entity is anything in the real world that can be entity. It is a conceptual representation of a category of entities that distinctly identified. It could be a person, place, share common characteristics, often represented as a table in a object, event, or concept. Entities are objects that relational database. The entity type defines the attributes (fields) that describe the entity but does not contain the actual data itself. we want to store data about in a database. Example of an Entity Type: Example of an Entity: In the example of an online shopping system, the entity type for Consider an online shopping system. One of the Customer would describe what kind of information (attributes) we store about customers. entities could be Customer. Entity Type: Customer Entity Name: Customer ◦ Attributes CustomerID (Primary Key), FirstName, LastName. Email, PhoneNumber, Address Attributes: CustomerID, FirstName, LastName, Email, PhoneNumber, Address The Customer entity type is essentially a template or structure that outlines the attributes for storing customer data, without yet containing specific customer information. In this case, "Customer" is an entity because it represents a real-world object (the person shopping) that we want to store data about.  Cardinality In terms of instances, cardinality in database management refers to the number of unique instances of one entity that can be associated with a single instance of another entity through a relationship. One-to-One (1:1): Each instance of one entity can be associated with only one instance of another entity, and vice versa. One-to-Many (1:N): Each instance of one entity can be associated with multiple instances of another entity, but each instance of the other entity can be associated with only one instance of the first entity. Many-to-One (N:1): Multiple instances of one entity can be associated with a single instance of another entity, but each instance of the other entity can be associated with only one instance of the first entity. Many-to-Many (N:M): Multiple instances of one entity can be associated with multiple instances of another entity. Instance An instance is a specific, identifiable occurrence of an entity. It is a row in a table, where actual data is stored, representing a specific example of the entity. In other words, an instance is a real-world example of an entity stored in the database. Example of an Instance: For the Customer entity type, an instance would be a specific customer record, such as a person who has signed up for the online shopping system. Instance: ◦ CustomerID: 001 ◦ FirstName: John ◦ LastName: Doe ◦ Email: [email protected] ◦ PhoneNumber: 555-1234 ◦ Address: 123 Elm Street In this case, John Doe is an instance of the Customer entity type. The entity type provides the structure, and the instance provides the actual data for a specific customer. Statement structure of Business Rules Are statements that define or constraint some aspect of the business Syntax of business rules: ENTITY_1 Relationship_Verb_Phrase number ENTITY_2 ENTITY_2 Relationship_Verb_Phrase number ENTITY_1 where Relationship_Verb_Phrase = Cardinality_adverb + Relationship_Verb Statement structure of Business Rules (…) Example: Cardinality Relationship Adverb verb What are the Keys in DBMS? A key in DBMS is an attribute or a set of attributes that help to uniquely identify a tuple (or row) in a relation (or table). Keys are also used to establish relationships between the different tables and columns of a relational database. Types of Keys in DBMS Primary Key Candidate Key Foreign Key Composite Key Surrogate Key Primary Key It is the first key used to identify one and only one instance of an entity uniquely. Employee Example: Employee_ID Primary Key Employee_Name Passport_Number License_number SSN Candidate Key A candidate key is an attribute or set of attributes that can uniquely identify a tuple. Except for the primary key, the remaining unique attributes are considered as a candidate key. The candidate keys are as strong as the primary key. Example: Foreign Key Foreign keys are the column of the table used to point to the primary key of another table. Example : Employee Department Employee_ID Department_ID Employee_Name Department_Name Department_ID Foreign Key Composite Key A composite key is a key that consists of two or more columns in a table. It is used when a single column cannot guarantee uniqueness, but a combination of columns does. Each combination of values in the composite key must be unique. Surrogate Key oA surrogate key is a unique identifier generated by a database system to uniquely distinguish each row or record in a table, especially when no natural or meaningful identifier is available. oit's just a number or code created for the sole purpose of uniquely identifying data in the table. oIt is an auto-incremented integer Customer CREATE TABLE Employees ( Customer_I employee_id INT AUTO_INCREMENT Customer_ID D PRIMARY KEY, Surrogate first_name VARCHAR(50), First_Name Key last_name VARCHAR(50), Date_of_Birth hire_date DATE ); ERD Entity-Relationship Diagrams (ERD) are visual representations of the relationships between different entities in a system. To create an ERD from business rules or scenario-based questions, we'll follow these steps: Identify Entity Types These are typically nouns in your scenario or business rule. For instance, "Customer," "Order," "Product," etc. Identify Relationships Look for verbs or associations between the entities. For instance, "Customer 'places' an Order." ERD (…) Identify Attributes These are the details or properties of your entities. For example, "Customer" might have attributes like "CustomerID," "Name," "Address," etc. Determine Cardinality This is about understanding how many of one entity relate to how many of another entity. For instance, one customer can place many orders, but each order is placed by one customer. This is a "one to many" relationship. Symbols used Entities : Relationships: Attributes: Cardinality: Crow’s Notation Now, let's create a simple ERD from a business scenario Scenario: A university has multiple departments. Each department offers several courses. Students can enroll in multiple courses. Every course has one instructor, but instructors can teach multiple courses. Business Rules 1.Each department can have multiple courses. 2.Each student can enroll in multiple courses. 3.Each course enrollment is tied to a specific semester. 4.Every course is taught by one instructor. 5.Instructors can teach multiple courses. Entity Types 1.Department 2.Course 3.Student 4.Instructor Identify attributes Relationships & Cardinality 1.Department - Course: One-to-many (One department offers many courses) 2.Student - Course: Many-to-many (Students can enroll in multiple courses and a course can have multiple students. 3.Instructor - Course: One-to-many (One instructor teaches many courses)  ERD Departme offe teach Course Instructor nt rs es Enro Student llme nt Associati ve Entity THE END