00:00

Fundamentals of Relational Databases and SQL

A database is an organized collection of data stored in secondary memory. Database applications facilitate information retrieval, while the client/server model partitions tasks between servers and clients. A Database Management System (DBMS) efficiently stores and manages data, with popular examples being Microsoft SQL Server and Oracle. Relational databases organize data as tables, utilizing primary and foreign keys for data integrity. Entity-Relationship Diagrams depict table relationships, including one-to-many, one-to-one, and many-to-many relationships. SQL (Structured Query Language) is the standard for querying and manipulating data.

pastrian
Download Presentation

Fundamentals of Relational Databases and SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ESSENTIALS OF RELATIONAL DATABASES AND SQL BY ANTONIO F. HUERTAS DEPARTMENT OF COMPUTER SCIENCE UNIVERSITY OF PUERTO RICO AT BAYAMÓN

  2. WHAT IS A DATABASE? • A database is an organized collection of data, normally stored in secondary memory. • A well-designed database is easily searchable and scalable. • Modern databases require complex data and file structures –with their corresponding algorithms– to ensure easy access and manipulation.

  3. WHAT IS A DATABASE APPLICATION • A database application is a computer program whose primary purpose is entering and retrieving information from a computerized database. • Most modern information systems are database applications. • A characteristic of modern database applications is that they facilitate simultaneous updates and queries from multiple users since they follow the client/server model.

  4. CLIENT/SERVER MODEL • The client/server model is a distributed application structure that partitions tasks between the providers of a resource or service, called servers, and service requesters, called clients. • Often clients and servers communicate over a computer network on separate hardware, but both client and server may reside in the same system. • An important issue in the client/server model is concurrency management. When two or more users retrieve the data in the same row of a database table at the same time, it is called a concurrency conflict.

  5. WHAT IS A DATABASE MANAGEMENT SYSTEM? • A database management system (DBMS) provides mechanisms for efficiently storing, organizing, retrieving and modifying data for software. • DBMSs frequently provide database-server functionality that allow clients access to a database. • Examples of popular DBMSs are Microsoft SQL Server, Oracle and MySQL.

  6. WHAT IS A RELATIONAL DATABASE? • Today’s most popular DBMSs manage relational databases, which organize data simply as tables with rows and columns. • This is a logical representation of data that allows the data to be accessed independently of its physical structure.

  7. WHAT IS A RELATIONAL DATABASE? (CONT.) • The following are tables for departments and employees in a company (taken from C# for Programmers by Deitel and Deitel): Departments Employees EmployeeID Name Salary DepartmentID 23603 Jones 1100.00 413 DepartmentID 413 Name Information Tech 24568 Kerwin 2000.00 413 34589 Larson 1800.00 642 611 Human Resources 35761 Myers 1400.00 611 642 Accounting 47132 Neumann 9000.00 413 78321 Stephens 8500.00 611

  8. WHAT ARE PRIMARY KEYS? • Each row in a table must have a primary key –a column (or group of columns) requiring a unique value that cannot be duplicated in other rows. • For example, the DepartmentID is the primary key of the Departments table and the EmployeeID is the primary key of the Employees table.

  9. WHAT ARE FOREIGN KEYS? • On the other hand, a foreign key is a column (or group of columns) in one table that corresponds to the primary key of another table. • For example, the DepartmentID in the Employees table is a foreign key that references the DepartmentID in the Departments table. • When a table is related to another one, a foreign key is used to represent this link.

  10. WHAT IS DATA INTEGRITY? • Data integrity is the maintenance of, and the assurance of the accuracy and consistency of, data over its entire life-cycle. The main types are: • Entity integrity is the requirement that every table must have a primary key that is unique and cannot be null. • Referential integrity is the requirement that every foreign key in one table must refer to valid and non-null primary key in another table. • Domain integrity is the requirement that all values in each column must belong to the same domain. This includes data type, length, range, default, and other details. • Business integrity is the requirement that the database must enforce user- defined business rules, regulations, policies, and procedures.

  11. WHAT IS AN ENTITY-RELATIONSHIP DIAGRAM? • An entity-relationship diagram shows the tables in the database and the relationships among them. • The most common type of relationship among tables is the one-to-many relationship (represented as 1-∞ or 1-*). • For example, this shows that each department may have many employees (but each employee belongs to only one department): 1 * Department Employee

  12. WHAT IS AN ENTITY-RELATIONSHIP DIAGRAM? (CONT.) • Another relationship among tables is the one-to-one relationship (represented as 1-1). • For example, this shows that each person may have only one passport (and each passport belongs to only one person): 1 1 Person Passport

  13. WHAT IS AN ENTITY-RELATIONSHIP DIAGRAM? (CONT.) • The third kind of relationship among tables is the many-to-many relationship (represented as ∞-∞ or *-*). • For example, this shows that each book may have many authors (and each author may have written many books): * * Book Author

  14. WHAT IS SQL? • SQL (Structured Query Language) is the international standard declarative computer language used almost universally with relational databases. • It is used as a Data Manipulation Language to perform queries (i.e., to request information that satisfies given criteria) and to manipulate data in a database (i.e., to insert, update or delete rows in a table). • It can be also used as a Data Definition Language to create and describe database tables and other database objects, such as views.

  15. HOW TO PERFORM QUERIES USING SQL? • The following table lists some common SQL keywords used in statements that query a database. SQL keyword Description SELECT Retrieves data from one or more tables. FROM Specifies the tables involved in a query. Required in every query. WHERE Specifies optional criteria for selection that determine the rows to be retrieved. Specifies optional criteria for ordering rows (e.g., ascending, descending). ORDER BY [INNER] JOIN Specifies optional operator for merging rows from multiple tables that have matching values.

  16. SOME EXAMPLES OF QUERIES • Which employees are in the database? SELECT * FROM Employees • What is the ID, name and salary of each employee? SELECT EmployeeID, Name, Salary FROM Employees • What are the ID, names and salaries of all employees from the Information Tech department? SELECT EmployeeID, Name, Salary FROM Employees WHERE DepartmentID = '413' • What are the IDs, names and salaries of all employees that earn $2000 or more? SELECT EmployeeID, Name, Salary FROM Employees WHERE Salary >= 2000

  17. SOME EXAMPLES OF QUERIES • What is the ID, name and salary of each employee, ordered by salary? SELECT EmployeeID, Name, Salary FROM Employees ORDER BY Salary • What are the IDs, names, salaries and departments of each employee? SELECT EmployeeID, Employees.Name, Salary, Departments.Name FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID • How many salaried employees are? What is the sum and average, minimum and maxixmum of all their salaries? SELECT COUNT(*) AS Count, SUM(Salary) AS Total, AVG(Salary) AS Average, MIN(Salary) AS Minimum, MAX(Salary) AS Maximum FROM Employees

  18. HOW TO MANIPULATE DATA USING SQL? • The following table lists some common SQL keywords used in statements that perform data manipulation. SQL keyword Description INSERT Inserts rows into the specified table. UPDATE Updates rows in the specified table. DELETE Deletes rows from the specified table.

  19. SOME EXAMPLES OF DATA MANIPULATION • Insert a Marketing department with an ID of 789. INSERT INTO Departments (DepartmentID, Name) VALUES ('789', 'Marketing') • Insert a new employee with an ID of 98765, named Smith, with a monthly salary of $3500 and assigned to the Marketing department. INSERT INTO Employees (EmployeeID, Name, Salary, DepartmentID) VALUES ('98765', 'Smith', 3500.00, '789')

  20. SOME EXAMPLES OF DATA MANIPULATION • Update the data of the employee with ID 98765 so that he or she now has a monthly salary of $4000. UPDATE Employees SET Salary = 4000.00 WHERE EmployeeID = '98765' • Delete the data of the employee with ID 98765. DELETE FROM Employees WHERE EmployeeID = '98765' • Delete the data of the department with ID 789. DELETE FROM Departments WHERE DepartmentID = '789'

More Related