Database Management System - Chapter 1: Introdution - Trần Thị Kim Chi

1Giảng viên: Trần Thị Kim Chi CHAPTER 1 24 What is a Database1 Introduction SQL Server 3 2 What is Database Management System Introduction about SQL Server 20085 SQL Server 2008 Database - Object6 Questions7 SQL Server Services & Tools 3• Data is raw, unorganized facts that need to be processed. Data are any facts, numbers, or text that can be processed by a computer. • Information – When data is processed, organized, structured or presented in a given context so as to make it useful, it is called information Data and Information1 Data (dữ liệu) và information (thông tin) DỮ LIỆU (DATA) THÔNG TIN (INFORMATION) XỬ LÝ 4Data and Information1 Data (dữ liệu) và information (thông tin) Thông tin: dữ liệu trong ngữ cảnh STT Mã sinh viên Họ và tên sinh viên Lớp Tuổi 1 10273 Nguyễn Văn Hoà CDTH7 20 2 00298 Nguyễn Minh Tâm CDTH7 19 151 50542 Hồ Xuân Phương TCTH33 18 152 50075 Lê Việt Dũng CNTH34 20 Dữ liệu 1 10273 Nguyễn Văn Hoà CDTH7 20 2 00298 Nguyễn Minh Tâm CDTH7 19 151 50542 Hồ Xuân Phương TCTH33 18 152 50075 Lê Việt Dũng CNTH34 20 5 A data model is a “description” of both a container for data and a methodology for storing and retrieving data from container.  Data model is  Not a thing  You cannot touch it  Data model are abstractions, mathematical algorithms & Concepts. Data Model1 6• A database a set of data that has a regular structure and that is organized in such a way that a computer can easily find the desired information. What is a Database1 Database Application Program 2 Application Program 1 Users Application Programs 7Database System Model1 Database management systems follow particular models (known as database models) to store and manipulate data. A database model is characterized by: 1. The way it stores data : STRUCTURE 2. The way data in the structure are manipulated: OPERATIONS 8• Persistent: – Should be able to store all kinds of data that exists in this real world. • Interrelated: – Data and application should be isolated. – Should be able to relate the entities / tables in the database by means of a relation. i.e.; any two tables should be related. • Shared: – Multiple users should be able to access the same database, without affecting the other user. – Database should also provide security, – Database should also support ACID property. i.e.; while performing any transactions like insert, update and delete, database makes sure that the real purpose of the data is not lost. Characteristics of the Datatabase1 9Objective  Ensuring data completeness  Avoiding data redundancies  Facilitating data access Solution  Normalization • Reducing redundancies and improving data modification performance • Renormalizations is often done to enhance reporting performance (at the expense of disk space and redundancy)  Referential Integrity • Maintains the logical relationships between database objects Database Design1 10 Requirement analysis What does the user want? Conceptual database design Defining the entities and attributes, and the relationships between these The ER model  Physical database design Implementation of the conceptual design using a Database Management System Steps in Database Design1 ER model & Relational Data Model1 BundooraNorman8507 BalwynMary8452 BundooraGlen3936 KewRobert1108 SuburbNameId Student Takes 298507 238507 231108 211108 SNOSID CSCEVB29 MathsAlgebra18 CSCEDatabase23 CSCESystems21 DeptNameNo Subject Relational Database Entities: Student (Id, Name, Suburb) Subject (No, Name, Dept) Relationships: Student Subject Takes ER models view the world by entities and relationships Relational models view the world by relations 12 Entities Attributes Relations Tables Fields 1 ER model & Relational Data Model 13 • “A DBMS that manages data as collection of tables in which all data relationships are represented by common values in related tables.” •“A DBMS that follows all the twelve rules of CODD is called RDBMS” Id Name Suburb 1108 Robert Kew 3936 Glen Bundoo ra 8507 Norma n Bundoo ra 8452 Mary Balwyn Student Takes SID SNO 1108 21 1108 23 8507 23 8507 29 The Relational Database1 14 • Table: information about a single entity • Primary key: (set of) column(s) that uniquely identifies a record. • Foreign key: (set of) column(s) used to link table together Id Name Suburb 1108 Robert Kew 3936 Glen Bundoo ra 8507 Norma n Bundoo ra 8452 Mary Balwyn Student Takes SID SNO 1108 21 1108 23 8507 23 8507 29 The Relational Database1 TABLE Structure1 Attributes Cardinality Primary Key Tuples Supplier DomainDomain SCode SName Quantity City S1 Kamran 20 Lahore S2 Zafar 10 Islamabad S3 Azmat 40 Karachi S4 Abdul 34 Lahore S5 Nasir 25 Islamabad 16 The Relational Database1 MONHOC SINHVIEN MASV TEN MALOP TCTH01 Sơn TCTHA TCTH02 Bảo TCTHB TCTH03 Trang TCTHA MASV MAMH DIEM TCTH01 THVP 8 TCTH01 CSDL 6 TCTH01 CTDL 7 TCTH02 THVP 9 TCTH02 CSDL 8 TCTH03 THVP 10 MAMH TENMH TINCHI KHOA THVP Nhập môn TH 4 CNTT CSDL Cấu trúc dữ liệu 4 CNTT CTDL Toán rời rạc 3 TOAN KETQUA LOP MALOP TENLOP SISO TCTHA TCTH32A 80 TCTHB TCTH32B 65 TCTHC TCTH32C 82 17 Ví dụ: Để quản lý hóa đơn bán hàng ta cần xây dựng một CSDL gồm các bảng sau : • SanPham: Mô tả các thông tin về sản phẩm như Masp, Tensp, Donvitinh, DongiaMua, Slton. • KhachHang: Mô tả các thông tin về khách hàng như Makh, Tenkh, Diachi, DienThoai. • Nhanvien: Mô tả các thông tin về nhân viên như Manv, Honv, Tennv, Phai, Ngaysinh, Diachi, DienThoai, Hinh. • Hoá đơn: Mô tả các thông tin về hóa đơn như Mahd, LoaiHD, Makh, Manv, NgaylapHD, NgayGiaoNhanHang, DienGiai. • Chitiethoadon: Mô tả các thông tin chi tiết của từng hóa đơn như Mahd, Masp, Soluong, DongiaBan. Example: TABLE Structure1 18 Example: TABLE Structure1 19 Bài tập Việc quản lý nhân viên của một công ty như sau: • Công ty gồm nhiều nhân viên, mỗi nhân viên có một MANV duy nhất, mỗi MANV xác định Hoten, Phai, NgaySinh, Phongban. Mỗi nhân viên chỉ do một phòng ban quản lý. • Mỗi phòng ban có một MAPB duy nhất, mỗi MAPB xác dịnh TenPB, DiaDiem, MaTP (Mã người phụ trách), KPHD(Kinh phí hoạt động), DT (Doanh thu của từng phòng ban). • Nhân viên được chia làm hai loại nhân viên. Nhân viên hành chánh và nhân viên tham gia sản xuất. Nhân viên hành chánh làm việc theo giờ hành chánh. Nhân viên tham gia sản xuất thì làm nhiều công việc khác nhau. • Mỗi công việc có một mã MACV, Macv xác định TENCV, DONGIA. Công ty có tối đa 3 công việc. • Hàng tháng công ty chấm công cho nhân viên để tính lương cho nhân viên như sau: – Nhân viên hành chánh chấm theo số ngày làm trong tháng. Mỗi nhân viên một tháng phải làm tối thiểu 25 ngày công. Lương tháng được tính theo công thức: SoNC*LCB +Thuong – Nhân viên sản xuất thì sẽ chấm công theo số lượng của mỗi công việc trong một tháng. Mỗi nhân viên phải làm ít nhất một công việc trong một tháng. Mỗi công việc số lượng nhân viên làm phải lớn hơn 10. Lương tháng được tính theo công thức: Tổng SoLuong*Dongia của mỗi công việc +Thuong Exercise1 20 Bài tập Câu hỏi: • Xây dựng mô hình thực thể kết hợp gồm: Xác định các thực thể, thuộc tính, khóa, các mối kế hợp và vẽ mô hình thực thể kết hợp • Chuyển mô hình thực thể về lược đồ cơ sở dữ liệu quan hệ và xác định các khóa • Xác định các mối quan hệ và các ràng buộc của lược đồ cơ sở dữ liệu trên Exercise1 21 Advantages • Reduced data redundancy • Reduced updating errors and increased consistency • Greater data integrity and independence from applications programs • Improved data access to users through use of host and query languages • Improved data security • Reduced data entry, storage, and retrieval costs • Facilitated development of new applications program Advantages of Database1 22 Disadvantages • Database systems are complex, difficult, and time- consuming to design • Substantial hardware and software start-up costs • Damage to database affects virtually all applications programs • Extensive conversion costs in moving form a file-based system to a database system • Initial training required for all programmers and users Disadvantages of Database1 23 • DataBase Management System (DBMS) – A database management system (DBMS) is system software for creating and managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data. DBMS – DataBase Management System2 24 DBMS – DataBase Management System2 Components of Database System • Users : Users may be of various type such as DB administrator, System developer and End users. • Database application : Database application may be Personal, Departmental, Enterprise and Internal • DBMS : Software that allow users to define, create and manages database access, Ex: MySql, Oracle etc. • Database : Collection of logical data. 25 CHARACTERISTICS OF THE DATABASE MANAGEMENT SYSTEM • Data Independence: The DBMS provides an abstract view of the data that hides such details. • Efficient Data Access: A DBMS utilizes a variety of sophisticated techniques to store and retrieve data efficiently. • Data Integrity and Security: If data is always accessed through the DBMS, the DBMS can enforce integrity constraints. 2 DBMS – DataBase Management System 26 CHARACTERISTICS OF THE DATABASE MANAGEMENT SYSTEM • Data Administration: When several users share the data, Experienced professionals who understand the nature of the data being managed, and how different groups of users use it, can be responsible for organizing the data representation to minimize redundancy and for fine tuning the storage of the data to make retrieval efficient. • Concurrent Access and Crash Recovery: A DBMS schedules concurrent accesses to the data in such a manner that users can think of the data as being accessed by only one user at a time. Further, the DBMS protects users from the effects of system failures. 2 DBMS – DataBase Management System 27 Functions of DBMS • Provides data Independence • Concurrency Control • Provides Recovery services • Provides Utility services • Provides a clear and logical view of the process that manipulates data. 2 DBMS – DataBase Management System 28 Quan hệ Architecture of DBMS 2 29 Quan hệ Architecture of DBMS 2 • Database (Data) Tier: the database resides along with its query processing languages. We also have the relations that define the data and their constraints at this level. • Application (Middle) Tier: reside the application server and the programs that access the database. For a user, this application tier presents an abstracted view of the database. The application layer sits in the middle and acts as a mediator between the end-user and the database. • User (Presentation) Tier − End-users operate on this tier and they know nothing about any existence of the database beyond this layer. At this layer, multiple views of the database can be provided by the application. All views are generated by applications that reside in the application tier. 30 SERVICES OF DBMS • Data Storage Management • Data Manipulation Management • Data Definition Services • Data Dictionary/System Catalog Management • Database Communication Interfaces • Authorization / Security Management • Backup and Recovery Management • Concurrency Control Service • Transaction Management • Database Access and Application Programming Interfaces 2 DBMS – DataBase Management System 31 Phần mềm có bản quyền • 4th Dimension • ANTs Data Server • Dataphor • Daffodil database • DB2 • FileMaker Pro • Informix • InterBase • Matisse [1] • Microsoft Access • Microsoft SQL Server • Mimer SQL • NonStop SQL • Oracle • Sand Analytic Server (trước đây là Nucleus) • SmallSQL [2] • Sybase ASA (trước đây là Watcom SQL) • Sybase • Sybase IQ • Teradata • ThinkSQL [3] • VistaDB [4] Quan hệ Types of DBMS2 Phần mềm miễn phí hoặc nguồn mở • Cloudscape • Firebird • HSQLDB • Ingres (cơ sở dữ liệu) • MaxDB • MonetDB • MySQL • PostgreSQL • SQLite • tdbengine 32 Advantages of a DBMS • Segregation of applicaion program. • Minimal data duplicacy. • Easy retrieval of data. • Reduced development time and maintainance need. 2 DBMS – DataBase Management System 33 DisAdvantages of a DBMS • Complexity • Costly • Large in size 2 DBMS – DataBase Management System 34 • RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. • A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Table 1 Key Data.. Table 2 Key Data... Quan hệ Database Relational Database Management System2 35 DBMS vs. RDBMS2 DBMS RDBMS The concepts of relationships is missing in a DBMS. If it exits it is very less. It is based on the concept Of relationships Speed of operation is very slow Speed of operation is very Fast Hardware and Software requirements are minimum Hardware and Software requirements are High Platform used is normally DOS Platform used can be any DOS, UNIX,VAX,VMS, etc Uses concept of a file Uses concept of table DBMS normally use 3GL RDBMS normally use a 4GL Examples are dBase, FOXBASE, etc Examples are ORACLE, INGRESS, SQL Server, Access, etc 36 • SQL stands for Structured Query Language • Microsoft SQL Server is a relational database management system developed by Microsoft • The primary query languages: T-SQL and ANSI SQL • SQL Server includes several components • Primary function: store and retrieve data as requested by other software applications on the same computer or on another computer across a network. Introduction SQL Server3 37 Client Application Client Net-Library Client SQL Server Relational Engine Storage Engine Server Local Database Database API (OLE DB, ODBC, DB-Library) Processor Memory Open Data Services Server Net-Libraries Query Result Set Result Set Query 1 2 3 4 5 Introduction SQL Server2 38 Client/Server Architecture Relational Database Management System SQL ServerClient Results Client Application OLAP OLTP Query Introduction SQL Server2 39 Ms SQL Server Components3 40 • SQL Server Integration Services (SSIS) is a platform for building high performance data integration solutions, including extraction, transformation, and load packages for data warehousing Ms SQL Server Components2 41 • SQL Server Database Engine is the core service for storing, processing, and securing data – This includes creating tables for storing data, and database objects such as indexes, views, and stored procedures for viewing, managing, and securing data Ms SQL Server Components2 42 • SQL Server Reporting Services (SSRS) includes: – A complete set of tools that you can use to create and manage reports, – An application programming interface (API) that allows developers to integrate or extend data and report processing in custom applications Ms SQL Server Components2 43 SQL Server Analysis Services : • Analysis Services delivers Online Analytical Processing and Data Mining functionality for Business Intelligence applications. Ms SQL Server Components2 44 SQL Server Service Broker : • Service Broker, internal or external processes can send and receive guaranteed, asynchronous messages by using extensions to Transact- SQL Data Manipulation Language (DML). • Messages can be sent to a queue in the same database as the sender, to another database in the same SQL Server instance, or to another SQL Server instance either on the same server or on a remote server. Ms SQL Server Components2 45 • SQL Server Replication is a set of technologies for copying and distributing data and database objects from one database to another, and then synchronizing between databases to maintain consistency Ms SQL Server Components2 46 • Full – Text Search lets users and applications run full-text queries against character-based data in SQL Server tables. • The full-text index includes one or more character-based columns in the table. Ms SQL Server Components2 47 • Notification Services is a platform for developing and deploying applications that generate and send notifications to subscribers. The notifications generated are personalized, timely messages that can be sent to a wide range of devices, and that reflect the preferences of the subscriber. Ms SQL Server Components2 48 • SQL Server Management Studio • SQL Server Configuration Manager • SQL Server Agent • SQL Profiler Ms SQL Server Tools2 49 • SQL Server Management Studio (SSMS) is used for configuring, managing, and administering all components within Microsoft SQL Server – This includes both script editors and graphical tools which work with objects and features of the server SQL Server Management Studio2 50 • SQL Server Configuration Manager is a tool provided with SQL Server 2008 for managing the services associated with SQL Server and for configuring the network protocols used by SQL Server. • Primarily, SQL Server Configuration Manager is used to start, pause, resume, and stop SQL Server services and to view or change service properties SQL Server Configuration Manager2 51 • SQL Server Agent is a scheduling tool integrated into SSMS that allows convenient definition and execution of scheduled scripts and maintenance jobs. SQL Server Agent2 52 • The SQL Server Profiler is a GUI interface to the SQL Trace feature of SQL Server that captures the queries and results flowing to and from the database engine. SQL Profiler2 53 Transa • Implementation of Entry-Level ANSI ISO Standard • Composing of 3 categories – Data Definition Language Statements (DDL) – Data Control Language Statements (DCL) – Data Manipulation Language Statements (DML) SQL Command3 54 SQL Command3 Command Description CREATE Creates a new table, a view of a table, or other object in database ALTER Modifies an existing database object, such as a table. DROP Deletes an entire table, a view of a table or other object in the database. DDL - Data Definition Language 55 SQL Command3 DML - Data Manipulation Language Command Description SELECT Retrieves certain records from one or more tables INSERT Creates a record UPDATE Modifies records DELETE Deletes records 56 SQL Command3 DCL - Data Control Language Command Description GRANT Gives a privilege to user REVOKE Takes back privileges granted from user 57 Evolution of SQL Server SQL Server 7.0 SQL Server 2005 SQL Server 2000 Realibility & Security Integrated Business Intelligence Performance and Scalability Automatic Tuning • Reliability and scalability advancements • Deep XML support • Data warehousing • SQL Server CE • 64 bit support • Re-architecture of relational server • First to include OLAP in database • Auto tuning • Ease-of-use Cross-release objective SQL Server 6.5 • Data warehousing • Internet support • Differentiation from Sybase SQL Server • Enterprise-class scalability • Programmability advancements • End-to-end business intelligence • Manageability • Support for multiple types of data SQL Server 2008 • Secure trusted platform for data • Productive policy-based management • Optimized and predictable system performance • Dynamic development • Beyond relational data • Pervasive Business Insight SQL Server Editions3 58 SQL Server 2008 Standard Edition • This is the version intended for the masses to medium-sized systems who don’t require the performance, s