PANGKALAN DATA TOPIK 2 ER DIAGRAM DAN

DATABASE

1. STM10163 DATABASE FUNDAMENTALS TOPIC 2 - DATABASE DESIGN THEORY

2. Entity An entity set is a set of same type of entities. Entity Examples Person EMPLOYEE, STUDENT, PATIENT Place STORE, WAREHOUSE Object MACHINE, PRODUCT, CAR Event SALE,REGISTRATION, RENEWAL Concept ACCOUNT, COURSE

3. Types of Entity Sets An entity set may be of the following two types-

4. 1. Strong Entity Set A strong entity set is an entity set that contains sufficient attributes to uniquely identify all its entities. In other words, a primary key exists for a strong entity set. Primary key of a strong entity set is represented by underlining it.

5. 2. Weak Entity Set A weak entity set is an entity set that does not contain sufficient attributes to uniquely identify its entities. In other words, a primary key does not exist for a weak entity set. However, it contains a partial key called as a  discriminator. Discriminator can identify a group of entities from the entity set. Discriminator is represented by underlining with a dashed line.

6. Example of Entity

7. Differences between Strong entity set and Weak entity set- Strong entity set Weak entity set A single rectangle is used for the representation of a strong entity set. A double rectangle is used for the representation of a weak entity set. It contains sufficient attributes to form its primary key. It does not contain sufficient attributes to form its primary key. A diamond symbol is used for the representation of the relationship that exists between the two strong entity sets. A double diamond symbol is used for the representation of the identifying relationship that exists between the strong and weak entity set. A single line is used for the representation of the connection between the strong entity set and the relationship. A double line is used for the representation of the connection between the weak entity set and the relationship set. Total participation may or may not exist in the relationship. Total participation always exists in the identifying relationship

8. Important Note W eak entity set is always present in total participation with the identifying relationship set.

9. Attributes Attributes are associated with an entity set. Attributes describe the properties of entities in the entity set. Based on the values of certain attributes, an entity can be identified uniquely.

10. Types of Attributes In ER diagram, attributes associated with an entity set may be of the following types-

11. 1. Simple Attributes Simple attributes are those attributes which can not be divided further.

12. 2. Composite Attributes Composite attributes are those attributes which are composed of many other simple attributes. T he attributes “Name” and “Address” are composite attributes as they are composed of many other simple attributes.

13. 3. Single Valued Attributes Single valued attributes are those attributes which can take only one value for a given entity from an entity set. A ll the attributes are single valued attributes as they can take only one specific value for each entity.

14. 4. Multi Valued Attributes Multi valued attributes are those attributes which can take more than one value for a given entity from an entity set. The attributes “Mob_no” and “Email_id” are multi valued attributes as they can take more than one values for a given entity.

15. 5. Derived Attributes Derived attributes are those attributes which can be derived from other attribute(s). The attribute “Age” is a derived attribute as it can be derived from the attribute “DOB”.

16. 6. Key Attributes Key attributes are those attributes which can identify an entity uniquely in an entity set. The attribute “Roll_no” is a key attribute as it can identify any student uniquely.

17. Relationship Association among two or more entities
Given a name that describes its function. Relationship name is an active or a passive verb

18. Degree of Relationships Degree: number of entity types that participate in a relationship On the basis of degree of a relationship set, a relationship set can be classified into the following types-

19. 1. Unary Relationship Set Unary relationship set is a relationship set where only one entity set participates in a relationship set . Example- One person is married to only one person

20. 2. Binary Relationship Set Binary relationship set is a relationship set where two entity sets participate in a relationship set. Example- Student is enrolled in a Course

21. 3. Ternary Relationship Set Ternary relationship set is a relationship set where three entity sets participate in a relationship set.   Example-

22. Mapping Cardinalities Mapping cardinalities define the number of association between two entities. Cardinalities one to one one to many many to many

23. One-To-One Relationship A  one-to-one  (1:1) relationship means that each record in Table A relates to one, and only one, record in Table B, and each record in Table B relates to one, and only one, record in Table A. received Personal Payroll 1 1

24. Each record in the Personal table is about one employee. That record relates to one, and only one, record in the Payroll table. Each record in the Payroll table relates to one, and only one, record in the Personal table. (This is what looking at it from both directions means.)

25. One-To-Many Relationship A  one-to-many  ( 1:M) relationship means a record in a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A . makes Customers Orders 1 M

26. The Customers table holds a unique record for each customer. Each customer can place many orders. Many records in the Orders table can relate to only one record in the Customers table. This is a one-to-many relationship ( 1:M) between the Customers table and the Orders table .

27. Many-To-Many Relationship In a many-to-many (M:N) relationship , a row in table A can have many matching rows in table B, and vice versa. assigned Employees Project M N

28. Each project can involve more than one employee and each employee can be working on more than one project. This constitutes a  many-to-many   (M:N ) relationship.

29. Difference Between Primary Key and Foreign Key Primary Key Foreign Key Primary key uniquely identify a record in the table. Foreign key is a field in the table that is primary key in another table. Primary Key can't accept null values. Foreign key can accept multiple null value. We can have only one Primary key in a table. We can have more than one foreign key in a table. The primary key of a particular table is the attribute which uniquely identifies every record and does not contain any null value. The foreign key of a particular table is simply the primary key of some other table which is used as a reference key in the second table. A primary key attribute in a table can never contain a null value. A foreign key attribute may have null values as well. Not more than one primary key is permitted in a table. A table can have one or more than one foreign key for referential purposes. Duplicity is strictly prohibited in the primary key; there cannot be any duplicate values. Duplicity is permitted in the foreign key attribute, hence duplicate values are permitted.

30. Dependency in Database An important concept associated with normalization is functional dependency which describes the relationship between attributes . Functional dependency can be divided into two types: Full functional dependency/Partial dependency (PD) Will be used to transform 1NF 2NF Transitive dependency (TD) • Will be used to transform 2NF 3NF

31. Dependency in Database Fully functional dependency - when an non-key attribute is determined by a whole part of a COMPOSITE primary key. Partial Dependency – when an non-key attribute is determined by a part, but not the whole of a COMPOSITE primary key. Transitive Dependency – when a non-key attribute determines another non-key attribute.