CSC 204 -DBMS-Convert.pdf

What is a Database? Definition, Meaning, Types with Example Before we learn about a database, let us understand – What is Data? In simple words, data can be facts related to any object in consideration. For example, your name, age, height, weight, etc. are some data related to you. A picture, image...

What is a Database? Definition, Meaning, Types with Example Before we learn about a database, let us understand – What is Data? In simple words, data can be facts related to any object in consideration. For example, your name, age, height, weight, etc. are some data related to you. A picture, image, file, pdf, etc. can also be considered data. What is Database? A database is a systematic collection of data. They support electronic storage and manipulation of data. Databases make data management easy. Let us discuss a database example: An online telephone directory uses a database to store data of people, phone numbers, and other contact details. Your electricity service provider uses a database to manage billing, client-related issues, handle fault data, etc. Let us also consider Facebook. It needs to store, manipulate, and present data related to members, their friends, member activities, messages, advertisements, and a lot more. We can provide a countless number of examples for the usage of databases. Database Components Database Components There are five main components of a database: Hardware The hardware consists of physical, electronic devices like computers, I/O devices, storage devices, etc. This offers the interface between computers and real-world systems. Software This is a set of programs used to manage and control the overall database. This includes the database software itself, the Operating System, the network software used to share the data among users, and the application programs for accessing data in the database. Data Data is a raw and unorganized fact that is required to be processed to make it meaningful. Data can be simple at the same time unorganized unless it is organized. Generally, data comprises facts, observations, perceptions, numbers, characters, symbols, images, etc. Procedure Procedure are a set of instructions and rules that help you to use the DBMS. It is designing and running the database using documented methods, which allows you to guide the users who operate and manage it. Database Access Language Database Access language is used to access the data to and from the database, enter new data, update already existing data, or retrieve required data from DBMS. The user writes some specific commands in a database access language and submits these to the database. What is DBMS? Database Management System (DBMS) is software for storing and retrieving users’ data while considering appropriate security measures. It consists of a group of programs that manipulate the database. The DBMS accepts the request for data from an application and instructs the operating system to provide the specific data. In large systems, a DBMS helps users and other third-party software store and retrieve data. DBMS allows users to create their own databases as per their requirements. The term “DBMS” includes the user of the database and other application programs. It provides an interface between the data and the software application. In this Database Management System, you will learn DBMS concepts like- Let us see a simple example of a university database. This database is maintaining information concerning students, courses, and grades in a university environment. The database is organized as five files:  The STUDENT file stores the data of each student  The COURSE file stores contain data on each course.  The SECTION stores information about sections in a particular course.  The GRADE file stores the grades which students receive in the various sections  The TUTOR file contains information about each professor. To define DBMS:  We need to specify the structure of the records of each file by defining the different types of data elements to be stored in each record.  We can also use a coding scheme to represent the values of a data item.  Basically, your Database will have 5 tables with a foreign key defined amongst the various tables. History of DBMS Here, are the important landmarks from the history of DBMS:  1960 – Charles Bachman designed the first DBMS system   1970 – Codd introduced IBM’S Information Management System (IMS)  1976- Peter Chen coined and defined the Entity-relationship model, also known as the ER model  1980 – Relational Model becomes a widely accepted database component  1985- Object-oriented DBMS develops.  1990s- Incorporation of object-orientation in relational DBMS.  1991- Microsoft ships MS access, a personal DBMS, and that displaces all other personal DBMS products.  1995: First Internet database applications  1997: XML applied to database processing. Many vendors begin to integrate XML into DBMS products. Characteristics of DBMS The characteristics and properties of a Database Management System:  Provides security and removes redundancy  Self-describing nature of a database system  Insulation between programs and data abstraction  Support of multiple views of the data  Sharing of data and multiuser transaction processing  Database Management Software allows entities and relations among them to form tables.  It follows the ACID concept ( Atomicity, Consistency, Isolation, and Durability).  DBMS supports a multi-user environment that allows users to access and manipulate data in parallel. DBMS vs. Flat File  Flat File Management DBMS System It does not support Multi-user access multi-user access Design to fulfill the need of small and large It is only limited to businesses smaller DBMS systems. Redundancy and Remove redundancy and Integrity. Integrity issues Expensive. But in the long term Total Cost It’s cheaper of Ownership is cheap No support for Easy to implement complicated complicated transactions transactions Users of DBMS Following are the various category of users of DBMS Component Name Task Application Programmers The Application programmers write programs in various programming languages to interact with databases. Database Administrators Database Admin is responsible for managing the entire DBMS system. He/She is called Database admin or DBA. End-Users The end users are the people who interact with the database management system. They conduct various operations on databases like retrieving, updating,  deleting, etc. Examples of DBMS Software The following are the major popular DBMS systems:  MySQL  Microsoft Access  Oracle  PostgreSQL  dBASE  FoxPro  SQLite  IBM DB2  LibreOffice Base  MariaDB  Microsoft SQL Server Application of DBMS Below are the database system applications areas: Sector Use of DBMS Banking For customer information, account activities, payments, deposits, loans, etc. Airlines For reservations and schedule information. Universities For student information, course  registrations, colleges, and grades. Telecommunication It helps to keep call records, monthly bills, maintain balances, etc. Finance For storing information about stock, sales, and purchases of financial instruments like stocks and bonds. Sales Use for storing customer, product & sales information. Manufacturing It is used to manage the supply chain and track the production of items. Inventories status in warehouses. HR Management For information about employees, salaries, payroll, deduction, generation of paychecks, etc. Types of DBMS Types of DBMS The main Four Types of Database Management Systems are:  Hierarchical database  Network database   Relational database  Object-Oriented database Hierarchical DBMS The Hierarchical Model was the first database management system model. This concept uses a hierarchical tree structure to organise the data. Data is Stored Hierarchically (top-down or bottom-up) format. Data is represented using a parent-child relationship. The hierarchy begins at the root, which contains root data, and then grows into a tree as child nodes are added to the parent node. This model accurately depicts several real-world relationships such as food recipes, website sitemaps, and so on. Example The following diagram depicts the relationship between the shoes available on a shopping website: Network Model The network database model allows each child to have multiple parents. It helps you to address the need to model more complex relationships like the orders/parts many-to-many relationship. In this model, entities are organized in a graph which can be accessed through several paths. Examples of Network Model in DBMS Let us take a basic example to visualize the structure of a network model in DBMS. Suppose we are designing the network model for the Students database. As we can see that the Subject entity has a relationship with both the Student entity and Degree entity. So there is an edge connecting the Subject entity with both Student and Degree. The Subject entity has two parents and the other two entities have one child entity. Other examples of the network model in DBMS can be:-   Store database (having relation between customers, manager, salesman, order, items, etc.).  Finance Department database (having relation between customers, products, invoices, payments, etc.). Relational Model Relational DBMS is the most widely used DBMS model because it is one of the easiest. This model is based on normalizing data in the rows and columns of the tables. Relational model stored in fixed structures and manipulated using SQL. Let's look at a scenario to understand the relational model: Consider a case where you wish to store the name, the CGPA attained, and the roll number of all the students of a particular class. This structured data can be easily stored in a table as described below: As we can notice from the above relation:  Any given row of the relation indicates a student i.e., the row of the table describes a real-world entity.  The columns of the table indicate the attributes related to the entity. In this case, the roll number, CGPA, and the name of the student. NOTE: A database implemented and organized in terms of the relational model is known as a relational database management system (RDBMS). Hence, the relational model describes how data is stored in relational databases. Object-Oriented Model The Object-Oriented Model in DBMS or OODM is the data model where data is stored in the form of objects. This model is used to represent real-world entities. The data and data relationship is stored together in a single entity known as an object in the Object Oriented Model. The Object-Oriented Database Management System is built on top of Object Oriented Model. As we have discussed earlier, we can use the Object Oriented Model in DBMS to store real-world entities. Here, we can store pictures, audio, video, and other types of data, which was previously impossible to store with the relational approach (Even though we can store video and audio in the relational database, it is generally not recommended). Example Now let us consider an example given below.   Here Transport, Bus, Ship, and Plane are objects.  Bus has Road Transport as the attribute.  Ship has Water Transport as the attribute.  Plane has Air Transport as the attribute.  The Transport object` is the base object and the Bus, Ship, and Plane objects derive from it. Take a look at another example- As you can see, here Student and Department are two different objects. Each one of them has its attributes and methods. They are linked by a common attribute Dept_no which establishes a relationship between objects. Advantages of DBMS   DBMS offers a variety of techniques to store & retrieve data  DBMS serves as an efficient handler to balance the needs of multiple applications using the same data  Uniform administration procedures for data  Application programmers are never exposed to details of data representation and storage.  A DBMS uses various powerful functions to store and retrieve data efficiently.  Offers Data Integrity and Security  The DBMS implies integrity constraints to get a high level of protection against prohibited access to data.  A DBMS schedules concurrent access to the data in such a manner that only one user can access the same data at a time  Reduced Application Development Time Disadvantage of DBMS DBMS may offer plenty of advantages, but it has certain flaws-  The cost of Hardware and Software of a DBMS is quite high, which increases the budget of your organization.  Most database management systems are often complex, so training users to use the DBMS is required.  In some organizations, all data is integrated into a single database that can be damaged because of electric failure or corruption in the storage media.  Using the same program at a time by multiple users sometimes leads to data loss.  DBMS can’t perform sophisticated calculations When not to use a DBMS system? Although DBMS system is useful, it is still not suited for the specific task mentioned below: Not recommended when you do not have the budget or the expertise to operate a DBMS. In such cases, Excel/CSV/Flat Files could do just fine. For Web 2.0 applications, it’s better to use NoSQL DBMS What is Database Architecture? A Database Architecture is a representation of DBMS design. It helps to design, develop, implement, and maintain the database management system. A DBMS architecture allows dividing the database system into individual components that can be independently modified, changed, replaced, and altered. It also helps to understand the components of a database. A Database stores critical information and helps access data quickly and securely. Therefore, selecting the correct Architecture of DBMS helps in easy and efficient data management. Types of DBMS Architecture There are mainly three types of DBMS architecture:  One Tier Architecture (Single Tier Architecture)  Two Tier Architecture  Three Tier Architecture Now, we will learn about different architecture of DBMS with diagram. 1-Tier Architecture 1 Tier Architecture in DBMS is the simplest architecture of Database in which the client, server, and Database all reside on the same machine. A simple one tier architecture example would be anytime you install a Database in your system and access it to practice SQL queries. But such architecture is rarely used in production. 1 Tier Architecture Diagram 2-Tier Architecture A 2 Tier Architecture in DBMS is a Database architecture where the presentation layer runs on a client (PC, Mobile, Tablet, etc.), and data is stored on a server called the second tier. Two tier architecture provides added security to the DBMS as it is not exposed to the end-user directly. It also provides direct and faster communication. 2 Tier Architecture Diagram In the above 2 Tier client-server architecture of database management system, we can see that one server is connected with clients 1, 2, and 3. Two Tier Architecture Example: A Contact Management System created using MS- Access. 3-Tier Architecture A 3 Tier Architecture in DBMS is the most popular client server architecture in DBMS in which the development and maintenance of functional processes, logic, data access, data storage, and user interface is done independently as separate modules. Three Tier architecture contains a presentation layer, an application layer, and a database server. 3-Tier database Architecture design is an extension of the 2-tier client-server architecture. A 3-tier architecture has the following layers: 1. Presentation layer (your PC, Tablet, Mobile, etc.) 2. Application layer (server) 3. Database Server 3 Tier Architecture Diagram The Application layer resides between the user and the DBMS, which is responsible for communicating the user’s request to the DBMS system and send the response from the DBMS to the user. The application layer(business logic layer) also processes functional logic, constraint, and rules before passing data to the user or down to the DBMS. The goal of Three Tier client-server architecture is:  To separate the user applications and physical database  To support DBMS characteristics  Program-data independence  Supporting multiple views of the data Three Tier Architecture Example: Any large website on the internet, including ebsu.edu.ng. DBMS SCHEMAS: INTERNAL, CONCEPTUAL, EXTERNAL Database systems comprise of complex data structures. Thus, to make the system efficient for retrieval of data and reduce the complexity of the users, developers use the method of Data Abstraction. There are mainly three levels of data abstraction: 1. Internal Level: Actual PHYSICAL storage structure and access paths. 2. Conceptual or Logical Level: Structure and constraints for the entire database 3. External or View level: Describes various user views Internal Level/Schema The internal schema defines the physical storage structure of the database. The internal schema is a very low-level representation of the entire database. It contains multiple occurrences of multiple types of internal record. In the ANSI term, it is also called “stored record’. Facts about Internal schema:  The internal schema is the lowest level of data abstraction  It helps you to keeps information about the actual representation of the entire database. Like the actual storage of the data on the disk in the form of records  The internal view tells us what data is stored in the database and how  It never deals with the physical devices. Instead, internal schema views a physical device as a collection of physical pages Conceptual Schema/Level The conceptual schema describes the Database structure of the whole database for the community of users. This schema hides information about the physical storage structures and focuses on describing data types, entities, relationships, etc. This logical level comes between the user level and physical storage view. However, there is only single conceptual view of a single database. Facts about Conceptual schema:  Defines all database entities, their attributes, and their relationships  Security and integrity information  In the conceptual level, the data available to a user must be contained in or  derivable from the physical level External Schema/Level An external schema describes the part of the database which specific user is interested in. It hides the unrelated details of the database from the user. There may be “n” number of external views for each database. Each external view is defined using an external schema, which consists of definitions of various types of external record of that specific view. An external view is just the content of the database as it is seen by some specific particular user. For example, a user from the sales department will see only sales related data. Facts about external schema:  An external level is only related to the data which is viewed by specific end users.  This level includes some external schemas.  External schema level is nearest to the user  The external schema describes the segment of the database which is needed for a certain user group and hides the remaining details from the database from the specific user group Goal of 3 level/schema of Database Here, are some Objectives of using Three schema Architecture:  Every user should be able to access the same data but able to see a customized view of the data.  The user need not to deal directly with physical database storage detail.  The DBA should be able to change the database storage structure without  disturbing the user’s views  The internal structure of the database should remain unaffected when changes made to the physical aspects of storage. Advantages Database Schema  You can manage data independent of the physical storage  Faster Migration to new graphical environments  DBMS Architecture allows you to make changes on the presentation level without affecting the other two layers  As each tier is separate, it is possible to use different sets of developers  It is more secure as the client doesn’t have direct access to the database business logic  In case of the failure of the one-tier no data loss as you are always secure by accessing the other tier Disadvantages Database Schema  Complete DB Schema is a complex structure which is difficult to understand for every one  Difficult to set up and maintain  The physical separation of the tiers can affect the performance of the Database RELATIONAL DATA MODEL IN DBMS | DATABASE CONCEPTS & EXAMPLE What is Relational Model? Relational Model (RM) represents the database as a collection of relations. A relation is nothing but a table of values. Every row in the table represents a collection of related data values. These rows in the table denote a real-world entity or relationship. The table name and column names are helpful to interpret the meaning of values in each row. The data are represented as a set of relations. In the relational model, data are stored as tables. However, the physical storage of the data is independent of the way the data are logically organized. Some popular Relational Database management systems are:  DB2 and Informix Dynamic Server – IBM  Oracle and RDB – Oracle  SQL Server and Access – Microsoft Relational Model Concepts in DBMS 1. Attribute: Each column in a Table. Attributes are the properties which define a relation. e.g., Student_Rollno, NAME,etc. 2. Tables – In the Relational model the, relations are saved in the table format. It is stored along with its entities. A table has two properties rows and columns. Rows represent records and columns represent attributes. 3. Tuple – It is nothing but a single row of a table, which contains a single record. 4. Relation Schema: A relation schema represents the name of the relation with its attributes. 5. Degree: The total number of attributes which in the relation is called the degree of the relation. 6. Cardinality: Total number of rows present in the Table. 7. Column: The column represents the set of values for a specific attribute. 8. Relation instance – Relation instance is a finite set of tuples in the RDBMS system. Relation instances never have duplicate tuples. 9. Relation key – Every row has one, two or multiple attributes, which is  called relation key. 10.Attribute domain – Every attribute has some pre-defined value and scope which is known as attribute domain Relational Integrity Constraints Relational Integrity constraints in DBMS are referred to conditions which must be present for a valid relation. These Relational constraints in DBMS are derived from the rules in the mini-world that the database represents. There are many types of Integrity Constraints in DBMS. Constraints on the Relational database management system is mostly divided into three main categories are: 1. Domain Constraints  2. Key Constraints 3. Referential Integrity Constraints Domain Constraints Domain constraints can be violated if an attribute value is not appearing in the corresponding domain or it is not of the appropriate data type. Domain constraints specify that within each tuple, and the value of each attribute must be unique. This is specified as data types which include standard data types integers, real numbers, characters, Booleans, variable length strings, etc. Example: Create DOMAIN CustomerName CHECK (value not NULL) The example shown demonstrates creating a domain constraint such that CustomerName is not NULL Key Constraints An attribute that can uniquely identify a tuple in a relation is called the key of the table. The value of the attribute for different tuples in the relation has to be unique. Example: In the given table, CustomerID is a key attribute of Customer Table. It is most likely to have a single key for one customer, CustomerID =1 is only for the CustomerName =” Google”. CustomerID CustomerName Status 1 Google Active CustomerID CustomerName Status 2 Amazon Active 3 Apple Inactive Referential Integrity Constraints Referential Integrity constraints in DBMS are based on the concept of Foreign Keys. A foreign key is an important attribute of a relation which should be referred to in other relationships. Referential integrity constraint state happens where relation refers to a key attribute of a different or same relation. However, that key element must exist in the table. Example: In the above example, we have 2 relations, Customer and Billing. Tuple for CustomerID =1 is referenced twice in the relation Billing. So we know CustomerName=Google has billing amount $300 Operations in Relational Model Four basic update operations performed on relational database model are Insert, update, delete and select.  Insert is used to insert data into the relation  Delete is used to delete tuples from the table.  Modify allows you to change the values of some attributes in existing tuples.  Select allows you to choose a specific range of data. Whenever one of these operations are applied, integrity constraints specified on the relational database schema must never be violated. Insert Operation The insert operation gives values of the attribute for a new tuple which should be inserted into a relation. Update Operation You can see that in the below-given relation table CustomerName= ‘Apple’ is updated from Inactive to Active. Delete Operation To specify deletion, a condition on the attributes of the relation selects the tuple to be deleted. In the above-given example, CustomerName= “Apple” is deleted from the table. The Delete operation could violate referential integrity if the tuple which is deleted is referenced by foreign keys from other tuples in the same database. Select Operation In the above-given example, CustomerName=”Amazon” is selected Best Practices for creating a Relational Model  Data need to be represented as a collection of relations  Each relation should be depicted clearly in the table  Rows should contain data about instances of an entity  Columns must contain data about attributes of the entity  Cells of the table should hold a single value  Each column should be given a unique name  No two rows can be identical  The values of an attribute should be from the same domain Advantages of Relational Database Model  Simplicity: A Relational data model in DBMS is simpler than the hierarchical and network model.  Structural Independence: The relational database is only concerned with data and not with a structure. This can improve the performance of the model.  Easy to use: The Relational model in DBMS is easy as tables consisting of rows and columns are quite natural and simple to understand  Query capability: It makes possible for a high-level query language like SQL to avoid complex database navigation.  Data independence: The Structure of Relational database can be changed without having to change any application.  Scalable: Regarding a number of records, or rows, and the number of fields, a database should be enlarged to enhance its usability. Disadvantages of Relational Model  Few relational databases have limits on field lengths which can’t be exceeded.   Relational databases can sometimes become complex as the amount of data grows, and the relations between pieces of data become more complicated.  Complex relational database systems may lead to isolated databases where the information cannot be shared from one system to another. Summary  The Relational database modelling represents the database as a collection of relations (tables)  Attribute, Tables, Tuple, Relation Schema, Degree, Cardinality, Column, Relation instance, are some important components of Relational Model  Relational Integrity constraints are referred to conditions which must be present for a valid Relation approach in DBMS  Domain constraints can be violated if an attribute value is not appearing in the corresponding domain or it is not of the appropriate data type  Insert, Select, Modify and Delete are the operations performed in Relational Model constraints  The relational database is only concerned with data and not with a structure which can improve the performance of the model  Advantages of Relational model in DBMS are simplicity, structural independence, ease of use, query capability, data independence, scalability, etc.  Few relational databases have limits on field lengths which can’t be exceeded. Entity Relationship (ER) Diagram Model with DBMS Example What is ER Diagram? ER Diagram stands for Entity Relationship Diagram, also known as ERD is a diagram that displays the relationship of entity sets stored in a database. In other words, ER diagrams help to explain the logical structure of databases. ER diagrams are created based on three basic concepts: entities, attributes and relationships. ER Diagrams contain different symbols that use rectangles to represent entities, ovals to define attributes and diamond shapes to represent relationships. At first look, an ER diagram looks very similar to the flowchart. However, ER Diagram includes many specialized symbols, and its meanings make this model unique. The purpose of ER Diagram is to represent the entity framework infrastructure. Entity Relationship Diagram Example What is ER Model? ER Model stands for Entity Relationship Model is a high-level conceptual data model diagram. ER model helps to systematically analyze data requirements to produce a well-designed database. The ER Model represents real-world entities and the relationships between them. Creating an ER Model in DBMS is considered as a best practice before implementing your database. ER Modeling helps you to analyze data requirements systematically to produce a well-designed database. So, it is considered a best practice to complete ER modeling before implementing your database. History of ER models ER diagrams are visual tools that are helpful to represent the ER model. Peter Chen proposed ER Diagram in 1971 to create a uniform convention that can be used for relational databases and networks. He aimed to use an ER model as a conceptual modeling approach. Why use ER Diagrams? Here, are prime reasons for using the ER Diagram  Helps you to define terms related to entity relationship modeling  Provide a preview of how all your tables should connect, what fields are going to be on each table  Helps to describe entities, attributes, relationships  ER diagrams are translatable into relational tables which allows you to build databases quickly  ER diagrams can be used by database designers as a blueprint for implementing data in specific software applications   The database designer gains a better understanding of the information to be contained in the database with the help of ERP diagram  ERD Diagram allows you to communicate with the logical structure of the database to users Facts about ER Diagram Model Now in this ERD Diagram Tutorial, let’s check out some interesting facts about ER Diagram Model:  ER model allows you to draw Database Design  It is an easy to use graphical tool for modeling data  Widely used in Database Design  It is a GUI representation of the logical structure of a Database  It helps you to identifies the entities which exist in a system and the relationships between those entities ER Diagrams Symbols & Notations Entity Relationship Diagram Symbols & Notations mainly contains three basic symbols which are rectangle, oval and diamond to represent relationships between elements, entities and attributes. There are some sub-elements which are based on main elements in ERD Diagram. ER Diagram is a visual representation of data that describes how data is related to each other using different ERD Symbols and Notations. Following are the main components and its symbols in ER Diagrams:  Rectangles: This Entity Relationship Diagram symbol represents entity types  Ellipses : Symbol represent attributes  Diamonds: This symbol represents relationship types   Lines: It links attributes to entity types and entity types with other relationship types  Primary key: attributes are underlined  Double Ellipses: Represent multi-valued attributes ER Diagram Symbols Components of the ER Diagram This model is based on three basic concepts:  Entities  Attributes  Relationships ER Diagram Examples For example, in a University database, we might have entities for Students, Courses, and Lecturers. Students entity can have attributes like Rollno, Name, and DeptID. They might have relationships with Courses and Lecturers. Components of the ER Diagram WHAT IS ENTITY? A real-world thing either living or non-living that is easily recognizable and nonrecognizable. It is anything in the enterprise that is to be represented in our database. It may be a physical thing or simply a fact about the enterprise or an event that happens in the real world. An entity can be place, person, object, event or a concept, which stores data in the database. The characteristics of entities are must have an attribute, and a unique key. Every entity is made up of some ‘attributes’ which represent that entity. Examples of entities:  Person: Employee, Student, Patient  Place: Store, Building  Object: Machine, product, and Car  Event: Sale, Registration, Renewal  Concept: Account, Course Notation of an Entity Entity set Student An entity set is a group of similar kind of entities. It may contain entities with attribute sharing similar values. Entities are represented by their properties, which also called attributes. All attributes have their separate values. For example, a student entity may have a name, age, class, as attributes. Example of Entities: A university may have some departments. All these departments employ various lecturers and offer several programs. Some courses make up each program. Students register in a particular program and enroll in various courses. A lecturer from the specific department takes each course, and each lecturer teaches a various group of students. Relationship Relationship is nothing but an association among two or more entities. E.g., Tom works in the Chemistry department. Entities take part in relationships. We can often identify relationships with verbs or verb phrases. For example:  You are attending this lecture  I am giving the lecture  Just loke entities, we can classify relationships according to relationship- types:  A student attends a lecture  A lecturer is giving a lecture. Weak Entities A weak entity is a type of entity which doesn’t have its key attribute. It can be identified uniquely by considering the primary key of another entity. For that, weak entity sets need to have participation. In above ER Diagram examples, “Trans No” is a discriminator within a group of transactions in an ATM. Let’s learn more about a weak entity by comparing it with a Strong Entity Strong Entity Set Weak Entity Set It does not have enough Strong entity set always has a primary attributes to build a key. primary key. It is represented by a It is represented by a rectangle symbol. double rectangle symbol. It contains a Primary key represented It contains a Partial Key by the underline symbol. which is represented by a Strong Entity Set Weak Entity Set dashed underline symbol. The member of a weak The member of a strong entity set is entity set called as a called as dominant entity set. subordinate entity set. In a weak entity set, it is a Primary Key is one of its attributes combination of primary which helps to identify its member. key and partial key of the strong entity set. The relationship between In the ER diagram the relationship one strong and a weak between two strong entity set shown entity set shown by using by using a diamond symbol. the double diamond symbol. The line connecting the The connecting line of the strong entity weak entity set for set with the relationship is single. identifying relationship is double. Attributes It is a single-valued property of either an entity-type or a relationship-type. For example, a lecture might have attributes: time, date, duration, place, etc. An attribute in ER Diagram examples, is represented by an Ellipse Types of Attributes Description Simple attributes can’t be divided any further. For example, a student’s Simple attribute contact number. It is also called an atomic value. It is possible to break down composite attribute. For example, a student’s full Composite attribute name may be further divided into first name, second name, and last name. This type of attribute does not include Derived attribute in the physical database. However, Types of Attributes Description their values are derived from other attributes present in the database. For example, age should not be stored directly. Instead, it should be derived from the DOB of that employee. Multivalued attributes can have more than one values. For example, a Multivalued attribute student can have more than one mobile number, email address, etc. Cardinality Defines the numerical attributes of the relationship between two entities or entity sets. Different types of cardinal relationships are:  One-to-One Relationships  One-to-Many Relationships  May to One Relationships  Many-to-Many Relationships 1.One-to-one: One entity from entity set X can be associated with at most one entity of entity set Y and vice versa. Example: One student can register for numerous courses. However, all those courses have a single line back to that one student. 2.One-to-many: One entity from entity set X can be associated with multiple entities of entity set Y, but an entity from entity set Y can be associated with at least one entity. For example, one class is consisting of multiple students. 3. Many to One More than one entity from entity set X can be associated with at most one entity of entity set Y. However, an entity from entity set Y may or may not be associated with more than one entity from entity set X. For example, many students belong to the same class. 4. Many to Many: One entity from X can be associated with more than one entity from Y and vice versa. For example, Students as a group are associated with multiple faculty members, and faculty members can be associated with multiple students. How to Create an Entity Relationship Diagram (ERD) Now in this ERD Diagram Tutorial, we will learn how to create an ER Diagram. Following are the steps to create an ER Diagram: Steps to Create an ER Diagram Let’s study them with an Entity Relationship Diagram Example: In a university, a Student enrolls 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 For them problem statement we know that,  A student can be assigned multiple courses  A Professor can deliver only one course Step 4) Identify Attributes You need to study the files, forms, reports, data currently maintained by the organization to identify attributes. You can also conduct interviews with various stakeholders to identify entities. Initially, it’s important to identify the attributes without mapping them to a particular entity. 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. If you think an attribute should belong to more than one entity, use a modifier to make it unique. Once the mapping is done, identify the primary Keys. If a unique key is not readily available, create one. Entity Primary Key Attribute Student Student_ID StudentName Professor Employee_ID ProfessorName Course Course_ID CourseName For Course Entity, attributes could be Duration, Credits, Assignments, etc. For the sake of ease we have considered just one attribute. Step 5) Create the ERD Diagram A more modern representation of Entity Relationship Diagram Example Best Practices for Developing Effective ER Diagrams Here are some best practice or example for Developing Effective ER Diagrams.  Eliminate any redundant entities or relationships  You need to make sure that all your entities and relationships are properly labeled  There may be various valid approaches to an ER diagram. You need to make sure that the ER diagram supports all the data you need to store  You should assure that each entity only appears a single time in the ER diagram  Name every relationship, entity, and attribute are represented on your diagram  Never connect relationships to each other  You should use colors to highlight important portions of the ER diagram What are Keys in DBMS? KEYS in DBMS is an attribute or set of attributes which helps you to identify a row(tuple) in a relation(table). They allow you to find the relation between two tables. Keys help you uniquely identify a row in a table by a combination of one or more columns in that table. Key is also helpful for finding unique record or row from the table. Database key is also helpful for finding unique record or row from the table. Example: Employee ID FirstName LastName 11 Andrew Johnson 22 Tom Wood 33 Alex Hale In the above-given example, employee ID is a primary key because it uniquely identifies an employee record. In this table, no other employee can have the same employee ID. Why we need a Key? Here are some reasons for using SQL key in the DBMS system.  Keys help you to identify any row of data in a table. In a real-world application, a table could contain thousands of records. Moreover, the records could be duplicated. Keys in RDBMS ensure that you can uniquely identify a table record despite these challenges.  Allows you to establish a relationship between and identify the relation between tables  Help you to enforce identity and integrity in the relationship. Types of Keys in DBMS (Database Management System) There are mainly Eight different types of Keys in DBMS and each key has it’s different functionality: 1. Super Key 2. Primary Key 3. Candidate Key 4. Alternate Key 5. Foreign Key  6. Compound Key 7. Composite Key 8. Surrogate Key Let’s look at each of the keys in DBMS with example:  Super Key – A super key is a group of single or multiple keys which identifies rows in a table.  Primary Key – is a column or group of columns in a table that uniquely identify every row in that table.  Candidate Key – is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes.  Alternate Key – is a column or group of columns in a table that uniquely identify every row in that table.  Foreign Key – is a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity.  Compound Key – has two or more attributes that allow you to uniquely recognize a specific record. It is possible that each column may not be unique by itself within the database.  Composite Key – is a combination of two or more columns that uniquely identify rows in a table. The combination of columns guarantees uniqueness, though individual uniqueness is not guaranteed.  Surrogate Key – An artificial key which aims to uniquely identify each record is called a surrogate key. These kind of key are unique because they are created when you don’t have any natural primary key. What is the Super key? A superkey is a group of single or multiple keys which identifies rows in a table. A Super key may have additional attributes that are not needed for unique identification. Example: EmpSSN EmpNum Empname 9812345098 AB05 Shown 9876512345 AB06 Roslyn 199937890 AB07 James In the above-given example, EmpSSN and EmpNum name are superkeys. What is a Primary Key? PRIMARY KEY in DBMS is a column or group of columns in a table that uniquely identify every row in that table. The Primary Key can’t be a duplicate meaning the same value can’t appear more than once in the table. A table cannot have more than one primary key. Rules for defining Primary key:  Two rows can’t have the same primary key value  It must for every row to have a primary key value.  The primary key field cannot be null.  The value in a primary key column can never be modified or updated if any foreign key refers to that primary key. Example: In the following example, StudID is a Primary Key. StudID Roll No First Name LastName Email 1 11 Tom Price [email protected] StudID Roll No First Name LastName Email 2 12 Nick Wright [email protected] 3 13 Dana Natan [email protected] What is the Alternate key? ALTERNATE KEYS is a column or group of columns in a table that uniquely identify every row in that table. A table can have multiple choices for a primary key but only one can be set as the primary key. All the keys which are not primary key are called an Alternate Key. Example: In this table, StudID, Roll No, Email are qualified to become a primary key. But since StudID is the primary key, Roll No, Email becomes the alternative key. StudID Roll No First Name LastName Email 1 11 Tom Price [email protected] 2 12 Nick Wright [email protected] 3 13 Dana Natan [email protected] What is a Candidate Key? CANDIDATE KEY in SQL is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes. The Primary key should be selected from the candidate keys. Every table must have at least a single candidate key. A table can have multiple candidate keys but only a single primary key. Properties of Candidate key:  It must contain unique values   Candidate key in SQL may have multiple attributes  Must not contain null values  It should contain minimum fields to ensure uniqueness  Uniquely identify each record in a table Candidate key Example: In the given table Stud ID, Roll No, and email are candidate keys which help us to uniquely identify the student record in the table. StudID Roll No First Name LastName Email 1 11 Tom Price [email protected] 2 12 Nick Wright [email protected] 3 13 Dana Natan [email protected] Candidate Key in DBMS What is the Foreign key? FOREIGN KEY is a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity. It acts as a cross-reference between two tables as it references the primary key of another table. Example: DeptCode DeptName 001 Science 002 English 005 Computer Teacher ID Fname Lname B002 David Warner B017 Sara Joseph B009 Mike Brunton In this key in dbms example, we have two table, teach and department in a school. However, there is no way to see which search work in which department. In this table, adding the foreign key in Deptcode to the Teacher name, we can create a relationship between the two tables. Teacher ID DeptCode Fname Lname B002 002 David Warner B017 002 Sara Joseph B009 001 Mike Brunton This concept is also known as Referential Integrity. What is the Compound key? COMPOUND KEY has two or more attributes that allow you to uniquely recognize a specific record. It is possible that each column may not be unique by itself within the database. However, when combined with the other column or columns the combination of composite keys become unique. The purpose of the compound key in database is to uniquely identify each record in the table. Example: OrderNo PorductID Product Name Quantity B005 JAP102459 Mouse 5 B005 DKT321573 USB 10 B005 OMG446789 LCD Monitor 20 B004 DKT321573 USB 15 B002 OMG446789 Laser Printer 3 In this example, OrderNo and ProductID can’t be a primary key as it does not uniquely identify a record. However, a compound key of Order ID and Product ID could be used as it uniquely identified each record. What is the Composite key? COMPOSITE KEY is a combination of two or more columns that uniquely identify rows in a table. The combination of columns guarantees uniqueness, though individually uniqueness is not guaranteed. Hence, they are combined to uniquely identify records in a table. The difference between compound and the composite key is that any part of the compound key can be a foreign key, but the composite key may or maybe not a part of the foreign key. What is a Surrogate key? SURROGATE KEYS is An artificial key which aims to uniquely identify each record is called a surrogate key. This kind of partial key in dbms is unique because it is created when you don’t have any natural primary key. They do not lend any meaning to the data in the table. Surrogate key in DBMS is usually an integer. A surrogate key is a value generated right before the record is inserted into a table. Fname Lastname Start Time End Time Anne Smith 09:00 18:00 Jack Francis 08:00 17:00 Anna McLean 11:00 20:00 Shown Willam 14:00 23:00 Above, given example, shown shift timings of the different employee. In this example, a surrogate key is needed to uniquely identify each employee. Surrogate keys in sql are allowed when  No property has the parameter of the primary key.  In the table when the primary key is too big or complicated. Difference Between Primary key & Foreign key Following is the main difference between primary key and foreign key: Primary Key Foreign Key It is a field in the table that Helps you to uniquely identify a record in is the primary key of the table. another table. Primary Key never accept null values. A foreign key may accept multiple null values. Primary Key Foreign Key A foreign key cannot Primary key is a clustered index and data automatically create an in the DBMS table are physically index, clustered or non- organized in the sequence of the clustered. However, you can clustered index. manually create an index on the foreign key. You can have the single Primary key in a You can have multiple table. foreign keys in a table. BASIC SQL SQL stands for Structured Query Language. It is a programming language so if you want to get a grasp of SQL, it is probably a good idea to first master the basics of programming (consider taking programming fundamentals course for non-programmers). Essentially, SQL is designed for managing data held in a relational database management system; it is the de facto standard database query language. SQL essentially contains two different components: a data definition language and a data manipulation language. The SQL language elements can be subdivided into four different language elements, including clauses, expression, predicates, queries, and statements. Queries are one of the most important aspects of SQL, allowing for the search and retrieval of data from a database based on specific criteria or characteristics. Overall, SQL can do a variety of different things. The language can execute queries against a database, retrieve data from a database, insert, update, or delete records in a database, create new databases, new tables in a database, or new stored procedures in a database, as well as set permissions on tables, procedures, and views in databases. Basically, you can do anything you might need to do in relation to a database with SQL. It is important to keep in mind that there are several different dialects of SQL, though it is essentially the closest thing to a standard query language that exists. SQL is used very widely in both industry and academia, very often for very large and complex databases. This is in part because SQL supports disturbed databases, or databases that are spread out over several different computer networks. SQL is a set-based, declarative query language, not an imperative language. With that being said, however, there are numerous extensions to SQL that add procedural programming language functionality, known as PSQLs. For example, Oracle is a widely used PSQL. To master SQL you first need to get a grasp of how, exactly, SQL code works. In particular, you will want to have a solid handle on SQL queries, as these are perhaps the most important function that SQL performs. Let’s take a look at some specific SQL query examples in order to understand how they work. SQL Commands o SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data. o SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set permission for users. Types of SQL Commands SQL SELECT Statement Essentially the SQL SELECT statement is used to make a query; it is what is used to choose the data that you want to retrieve from the database to your application, creating a table based on the commands given. This allows you to sort and withdraw data based on a variety of different characteristics. Once you are getting started learning the SQL SELECT statement it is probably best to keep things pretty simple. However, it is important to keep in mind that the SELECT statement has many different options and extensions that provide a great deal of flexibility. Basically, an SQL SELECT statement can be broken down into several distinct element, each beginning with a keyword. These elements include: SELECT, FROM, WHERE, and ORDER BY. SELECT... FROM The most basic SELECT statement has only two parts. This includes what column you want to select from as well as what table that column comes from. You will want to write the column name immediately following the SELECT statement and if you are retrieving from more than one column you will need to separate each column name with a comma. A basic statement will look like this: SELECT column name I, column name II, column name III FROM table name; WHERE The WHERE command essentially helps you to filter or to limit data. So in order for a piece of data to be fetched it must meet a certain condition. For example, let’s say you want to look at the data of all of the expats living in Lima, Peru. You’ve compiled a table called expat with all of the expat’s essential information—columns for their names, their ages, their occupations, their home country, etc. Let’s say that for this specific part of your project you are only interested in examining the ages and occupations of the expats whose home country is Germany. A basic select command would look something like this: SELECT Age, Occupation FROM Expat WHERE HomeCountry = “Germany”; Or let’s say you want this data for all of the expats in Lima, Peru except those whose home country is Germany. A basic select command would like this: SELECT Age, Occupation FROM Expat WHERE HomeCountry “Germany”; Of course, this is just a basic example. There are many more possibilities than this. If you are working with numerical data, for example, you can also give data ranges when using the WHERE command and you can also specify multiple conditions. The possibilities are numerous.