Database Lecture 1 - Entity Relationship Modeling PDF

Database Lecture 1 Entity Relationship Modeling What is Data? Data can be anything and everything. Any information or fact can be considered as data. Your name, age, data of birth or any other information such as your house address, bank balance, vehicle you drive or even the food...

Database Lecture 1 Entity Relationship Modeling What is Data? Data can be anything and everything. Any information or fact can be considered as data. Your name, age, data of birth or any other information such as your house address, bank balance, vehicle you drive or even the food you eat can be considered as data.  Data can be in any form such as an image, file, voice recording, video or even a plain text etc. For a school, data can be information related to its teachers, students or the subjects they teach. To summarize, data can be anything and everything and it can be in any format. What Is a Database? Database can be considered as a container filled with data or information which is electronically stored in a computer system. Data in any form can be stored into the database. Purpose of storing data in a database is so that it can be easily accessed, modified, protected and analyzed. Evolution of the database Evolution of database started in 1960’s when the first type of database were made which was the Flat File Database. Here the data was stored in simple files such as CSV file or fixed length files etc. Navigational databases such as the (which relied on a tree-like model and allowed only a one-to-many relationship), and the hierarchical database work database (a more flexible model that allowed multiple relationships), were the original systems used to store and manipulate data. Although simple, these early systems were inflexible. Evolution of the database Cont. In the 1980s, relational databases became popular, followed by object- oriented databases in the 1990s. More recently, NoSQL databases came about as a response to the growth of the internet and the need for faster speed and processing of unstructured data.  Today, cloud databases and self-driving databases are breaking new ground when it comes to how data is collected, stored, managed, and utilized. A cloud database is simply a database that is deployed in a cloud environment as opposed to an on-premises environment. This means that organizations can build databases without the purchase of the physical hardware and infrastructure needed for on-premise databases.  A database typically requires a comprehensive database What is a software program known as a database management system (DBMS). database A DBMS serves as an interface between the database and its end users or programs, allowing users to retrieve, update, and management manage how the information is organized and optimized. system A DBMS also facilitates oversight and control of databases, enabling a variety of administrative operations such as performance monitoring, tuning, and backup and recovery. (DBMS)? Some examples of popular database software or DBMSs include MySQL, Microsoft Access, Microsoft SQL Server, FileMaker Pro, Oracle Database, and dBASE. Entity-Relationship Diagram (ERD): is a graphical representation that depicts relationships among people, objects, places, concepts or events within an information technology (IT) system. Importance of ERDs and their uses Entity relationship diagrams provide a visual starting point for database design that can also be used to help determine information system requirements throughout an organization. After a relational database is rolled out, an ERD can still serve as a reference point, should any debugging or business process re- engineering be needed later. Steps to create ERD: Entity Identificati on Relationshi p Identificati on Cardinality Identificati on Identify attributes Create ERD  Definitions Entity - An entity is a thing that exists and is distinguishable -- an object, something in the environment. ( Types of entities: Weak- Regular) Entity Instance - An instance is a particular occurrence of an entity. For example, each person is an instance of an entity, each car is an instance of an entity, etc. Weak Entity Types An entity that does not have a key attribute A weak entity must participate in an identifying relationship type with an owner or identifying entity type Entities are identified by the combination of: A partial key of the weak entity type The particular entity they are related to in the identifying entity type Types of Attributes 1.Key 2. Multi-valued 3.Composite 4.Derived Key Attribute An attribute of an entity type for which each entity must have a unique value is called a key attribute of the entity type. For example, SSN of EMPLOYEE. A key attribute may be composite. For example, ID is a key of the applicant entity type with components (National_ID, Application_no). Candidate Key: An entity type may have more than one key.  Relationships: Cardinality constraint Relationships - A relationship is a connection between entity classes. The cardinality of a relationship indicates the number of instances in entity class E1 that can or must be associated with instances in entity class E2. One-One Relationship - (citizen – passport , One-Many Relationship - (student-Advisor, Customer- Order) Many- Many Relationship - (e.g. Student-Organization, Order-Products) Recursive Relationships - A relationship in which the same entity participates more than once. Recursive Relationships PARTICIPATION CONSTRAINT Participation Constraint is a type of relationship constraint. It describes that whether the existence of an entity is dependent on its relationship with another entity via the relationship type. There are two kinds of participation constraints. Total Participation (Existence dependency) : specifies that each entity present in the entity set must mandatorily participate in at least one relationship instance of that relationship set, for this reason, it is also called as mandatory participation It is represented using a double line between the entity set and relationship set  Example of total participation constraint It specifies that each student must be enrolled in at least one course where the “student” is the entity set and relationship “enrolled in” signifies total participation It means that every student must have enrolled at least in one course partial participation It specifies that each entity in the entity set may or may not participate in the relationship instance of the relationship set, is also called as optional participation It is represented using a single line between the entity set and relationship set in the ER diagram Example of partial participation A single line between the entities i.e courses and enrolled in a relationship signifies the partial participation, which means there might be some courses where enrollments are not made i.e enrollments are optional in that case An Example A company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. A department may have several locations. A department may control a number of projects, each of which has a unique name, a unique number, and a single location. A project must controlled by department. We store employee’s name, social security number, address, salary, gender and birth date. An employee must be assigned to one department and must work on one or more projects, which are not necessarily controlled by the same department. We keep track of the number of hours per week that an employee works on each project. We also keep track of the direct supervisor of each employee. We want to keep track of the dependents of each employee for insurance purposes. We keep each dependent’s first name, gender, birth date and relationship to that employee.