Entity Relationship Diagram What is a database A

Entity Relationship Diagram

What is a database ? • A database is absolutely an essential part of software systems. • A database is a collection of information that is organized so that it can be easily accessed, managed and updated. • Databases typically contain collections of data records or files. • The collections contain for example, information about sales transactions, products, students and customers to name a few.

Database Management System • Mostly data represents recordable facts. • Data helps in producing information, which is based on facts. • For example, if we have data about marks obtained by all students, we can then conclude about highest scorers and average marks. • A database management system stores data in such a way that it becomes easier to retrieve, manipulate and produce information.

Database Management System • A database management system provides the ability for many different users to share data and process resources. • But as there can be many different users, there are many different database needs. • How can a single, unified database meet the differing requirement of so many users? • A DBMS minimizes these problems by providing two views of the database data: a physical view and a logical view.

Logical and Physical View • The physical view deals with the actual, physical arrangement and location of data in the direct access storage devices. • Database specialists use the physical view to make efficient use of storage and processing resources. • The logical view/user’s view, of a database program represents data in a format that is meaningful to a user and to the software programs that process those data. • That is, the logical view tells the user, in user terms, what is in the database. • Thus the logical view refers to the way user views data, and the physical view to the way the data are physically stored and processed.

Relational Data Model • The relational data model represents everything in the database as being stored in the forms of tables (aka, relations).

Entity Relationship Diagram (ERD) • An Entity Relationship Diagram (ERD) is a graphical representation of an organization's data storage requirements. • It shows how “entities” such as people, objects or concepts relate to each other within a system. • Entity Relationship Diagrams are used to : – identify the data that must be acquired, stored and retrieved in order to support the business activities performed by an organization – understanding the relationships between entities so as to help root out any uncertainty or unnecessary processes within your organization.

Entity Relationship Diagram (ERD) • The ERD had three (3) main components: – entities of the database – attributes of the entities and relationships – relationships between the entities • There are several conventions used in ERD. • We will use the UML model in this lesson.

Entities • Each entity is given a name that reflects its contents. • The name is usually written in capital letters. • For example : – A STUDENT entity may represent all students who attend a university. – An EMPLOYEE entity might represent a collection of all the employees that work for an organization. • Individual members of an entity are called an entity occurrence or entity instance • Entities in an ERD correspond to a table. • Instances correspond to a row in a table. • CLUE : Entities are usually nouns

Each row contains data about a specific occurrence of the type of entity in the table.

Attributes • An attribute is a property or characteristic of an entity or relationship. • In the example, the attributes of STUDENT are Student. Id, First. Name, Last. Name, Date of Birth, Sex, Address and Phone.

Each column in a table contains information about a specific attribute of the entity.

A primary key is the attribute or combination of attributes that uniquely identifies a specific row in a table. In other words, there must not be two (or more) records that share the same value for the primary key attribute.

In some tables, two or more attributes may be joined to form the primary key.

STUDENTS Student ID Last Name First Name Phone No. Advisor No. 333 -33 -3333 Simpson Alice 333 -3333 1418 111 -11 -1111 Sanders Ned 444 -4444 1418 123 -45 -6789 Moore Artie 555 -5555 1503 ADVISORS Advisor No. Last Name First Name Office No. 1418 Howard Glen 420 1419 Melton Amy 316 1503 Zhang Xi 202 1506 Radowski J. D. 203 A foreign key is an attribute in one table that is a primary key in another table. It is a reference to a primary key in a table. Foreign keys are used to link tables together. It is used to identify the relationships between entities. Note that foreign keys need not be unique. Multiple records can share the same values.

STUDENTS Student ID Last Name First Name Phone No. Advisor No. 333 -33 -3333 Simpson Alice 333 -3333 1418 111 -11 -1111 Sanders Ned 444 -4444 1418 123 -45 -6789 Moore Artie 555 -5555 1503 ADVISORS Advisor No. Last Name First Name Office No. 1418 Howard Glen 420 1419 Melton Amy 316 1503 Zhang Xi 202 1506 Radowski J. D. 203 Other non-key attributes in each table store important information about the entity.

Relationship • A relationship between two entities signifies that the two entities are associated with each other somehow. • For example, a student might enroll in a course. • The entity Student is therefore related to Course, and a relationship is presented as a connector connecting between them.

Cardinality • Cardinality defines the possible number of occurrences in one entity which is associated with the number of occurrences in another. • For example – ONE team has MANY players. – When present in an ERD, the entity Team and Player are inter-connected with a one-to-many relationship.

One-to-One Relationship • In a one-to-one relationship, one row in a table is associated with one and only one row in another table. • For example, in a school database, each student has only one student ID, and each student ID is assigned to only one person. STUDENT Student. Id{PK} First. Name Last. Name Date. Of. Birth Sex Address 1… 1 CONTACTINFO Student. Id{PK} has Phone City

One-to-many • In a one-to-many relationship, one row in a table can be associated with one or more rows in another table. • For example, each department offers many courses but that course is offered by only that one department.

Many-to-many relationships • A many-to-many relationship occurs when multiple rows in a table are associated with multiple rows in another table. • For example, a many-to-many relationship exists between students and courses: – a student can enroll for many courses – a course has many students enrolled in it