人事资料记录表 Title16

1、Introduction to Database SystemsRuoming JinTTH 9:15 10:30pmSpring 2021rm MSB115Course GoalsThis course is an introduction to the design, use, and internal workings of database management system. Understanding Relational Model/Algebra: data is represented as a set of two dimensional tables and operat

2、ors are defined by manipulate the data. Mastering SQL programming and mySQL database: you will master the operations on a database system. Database design: how to build a real database for an real application? Web+Database: PHP and C+ access of mySQL. Database System Implementation: Storage/Indexing

3、/Transactions 1 & 5 separate a CS major with an IT professional 1, 2, 5 are the basics of a database system. ReferencesA. Silberschatz, H. F. Korth, S Sudarshan, Database System Concepts, 5th Ed., McGrow Hill, 2005Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer Widom, Database Systems, The Complet

4、e Book, Prentice Hall, 2002 ://ullman/dscb.htmlClass notesPrerequisitesCS 33001 Data StructuresCS31011 Discrete StructuresStructured Programming Language (C+)Software engineering topics related to project documentation and project designWorkload & RequirementsProject3 Exams during

5、 the semester and Final ExamProject 20% of the final grade 2 Exams 15% of the final grade per each exam 1 Exam 20% of the final grade Final 25% of the final grade Attendance 5%No late projects are acceptedA 91 100; B 80-90; C 7079; D - 64 Class ScheduleWeek 1 Database OverviewWeek 2,3 Relational Mod

6、el (exam 1) Week 4,5,6 SQL+ mySQL database (2 labs+exam 2) Week 7,8, 9 ER model+ Relational Database Design Theory (1 lab+ exam 3) - project assignmentWeek 10, 11,12 PHP programming + C+ database access (1 lab) Web 13, 14,15 Advanced topic (indexing/Query Procesing/Transaction management) + (1 lab)

7、Database OverviewFile Management vs Database Management (why do we need database?) Advantages of Database systems: storage persistence, programming interface, transaction managementData Model (What is Data?) Database Language (How to manipulate data?) DBMS Architecture and Database System Components

8、 (How can you build a billion-dollar software, like Oracle? Or you can get it free, mySQL?) Users classification (What you can do and what you cannot do?) Where are databases?You cannot avoid it and its everywhere!You can say it actually makes the current society and your life work!Banking/Credit ca

9、rd /Social Security InfoOnline shopping/bookingThe DBMS MarketplaceRelational DBMS companies Oracle, Sybase are among the largest software companies in the world.IBM offers its relational DB2 system. With IMS, a nonrelational system, IBM is by some accounts the largest DBMS vendor in the world.Micro

10、soft offers SQL-Server, plus Microsoft Access for the cheap DBMS on the desktop, answered by “lite systems from other competitors.OpenSource: mySQL, postgreSQLPre-Database Era: Stone Age of Data Imagine you want build an online shopping websiteMaintain products/categories (price, picture, properties

11、, )Customers accounts File is uninterpreted, unstructured collection of informationFile operations: delete, catalog, create, rename, open, close, read, write, find, Access methods: Algorithms to implement operations along with internal file organizationExamples: File of Customers, File of Products;

12、Access method: implementation of a set of operations on those filesC+ file programmingopen - open a file- specify how its opened (read/write) and type (binary/text) close - close an opened file read - read from a file write - write to a file seek - move a file pointer to somewhere in a file File Man

13、agement System ProblemsAny question (access) on the data is a small program! Data redundancyData is not isolated from the access implementation (different format) Multiple application (concurrent program) on the same fileConcurrent Program ExecutionProgram1What is the final value of the account AC?P

14、rogram2AC=AC-50AC=AC-100AC #103 450Security ProblemsAllow access to the file only to the authorized personnelAbility to restrict access to parts of the recordAbility to control operation usage by different usersProtection from unauthorized useProtection from the derivation of unauthorized informatio

15、nData IntegrityA database constraint is a logical constraint about the data expressed in a logical language. STUDENT.AGE 15If (STUDENT.CLASS =cs43005) then (STUDENT.PRIOR_CLASS =cs31001)Database is consistent if data at each time satisfies all integrity constraints. Input to any application is a set

16、 of consistent data. An application output is a set of consistent data.Collection of FilesHierarchical 60s70s80s90snowRelationalChoice for most new applicationsObject BasesKnowledge BasesNetworkAdvantages of DatabasesPersistent Storage Database not only provides persistent storage but also efficient

17、 access to large amounts of dataProgramming Interface Database allows users to access and modify data using powerful query language. It provides flexibility in data managementTransaction Management Database supports a concurrent access to the data Early Database ApplicationsAirline Reservation Syste

18、ms Data items are: single passenger reservations; Information about flights and airports; Information about ticket prices and tickets restrictions. Banking Systems Data items are accounts, customers, loans, mortgages, balances, etc. Failures are not tolerable. Concurrent access must be providedCorpo

19、rate Records Data items are: sales, accounts, bill of materials records, employee and their dependentsModern Database ApplicationsClient Server architectureDBMS serves as a server and client queries are sent to serversWhere to locate serversMultimedia ApplicationsMultidatabase ApplicationsData Wareh

20、ousesIts everywhere!Three Aspects to Studying DBMSs1. Modeling and design of databases.Allows exploration of issues before committing to an implementation.2. Programming: queries and DB operations like update.3. DBMS implementation.What Is Data ?Different view points:A sequence of characters stored

21、in computer memory or storageInterpreted sequence of characters stored in computer memory or storageInterpreted set of objectsThis maybe one of the most profound questions in computer science! It is still open and keep evolving! Data Levels and their RolesPhysical corresponds to the first view of da

22、ta: How data is stored, how is it accessed, how data is modified, is data ordered, how data is allocated to computer memory and/or peripheral devices, how data items are actually represented (ASCI, EBCDIC,)Conceptual corresponds to the second view of data: What we want the data to express and what r

23、elationships between data we must express, what “ story data tells, are all data necessary for the “story are discussed.View corresponds to the third view of data:What part of the data is seen by a specific applicationPhysical Data - ExamplePhysicalbenjamin631030000035000Jjames610336000375. . . . .

24、. . . . ExamplesConceptualTA Name char(10), Age char (3), Salary Fixed Dec(6); - Student Name char(10), Year-of_study char(3) GPA Fixed Dec(5,2);Examples- STUDENTS-TA Name char(25), Age char (3), Salary Fixed Dec(8,2), Year-of_study char(3) GPA Fixed Dec(3,2);A viewThree Level Data View Data Abstrac

25、tionsView1 View kConceptual View Of DataPhyisal Data Storage. . . . .Logical Data ModelsA collection of tools for describing data data relationshipsdata semanticsdata constraintsA Break-through : Relational ModelAn enterprise is represented as a set of relationsDomain is a set of atomic values. Each

26、 domain has a NULL value.Data type Description of a form that domain values can be represented. Relation is a subset of a cartesian product of one or more domainsThe elements of relations are called tuples. Each element in the cartesian product is called attribute.Relational ModelExample of tabular

27、data in the relational modelNameStudent-idStreetCitygpaJohnsonSmithJohnsonJonesSmith192-83-7465019-28-3746192-83-7465321-12-3123019-28-3746AlmaNorthAlmaMainNorthPalo AltoRyePalo AltoHarrisonRye4.03.45AttributesObject Oriented ModelAn enterprise is described as a collection of objects and a

28、collection of algorithms that work with objectsExample: Person is an object.Object is characterized by a set of public attributes. Applications may refer only to public attributes; private attributes . Algorithms that implement the object may refer to private attributes; a set of protected attribute

29、s and a set of methodsAttribute of an object can be another objectObjects are nested into a hierarchy and can inherit attributes of their parentsObject Oriented ModelOBJECT DATA MODEL1.Complex Objects Nested Structure (pointers or references)2.Encapsulation, set of Methods/Access functions3.Object I

30、dentity4.Inheritance Defining new classes like old classesObject model: usually find objects via explicit navigationAlso query language in some systemsExampleClass Person public: Person(); Person(); float GetSalary(); float PutSalary(float&); string Name; int SSN; date BirthDate; private: float sala

31、ry; Object-Oriented ModelData EncapsulationAn object contains both data and methods to work with the dataThe physical data representation is visible only to the object creator.The implementation details of methods are not visible to object usersAn interface of the object consists of public attribute

32、s and methodsEach object is characterized by an object identityData Manipulation LanguageLanguage for accessing and manipulating the data organized by the appropriate data modelTwo classes of languages Procedural user specifies what data is required and how to get those data Nonprocedural user speci

33、fies what data is required without specifying how to get those dataSQL is the most widely used query languageDatabase LanguagesFacultyNameDeptDepartmentDeptChairSQLSELECT Chair FROM Faculty, DepartmentWHERE F = “Ken NonameAND Faculty.Dept = Department.DeptData definition language (DDL) li

34、ke type definitions in C or C+Data Manipulation Language (DML)Query (SELECT)UPDATE SET = WHERE Data Definition LanguageSpecification notation for defining the database schemaE.g. create table account ( account-number char(10), balance integer)DDL compiler generates a set of tables stored in a data d

35、ictionaryData dictionary contains metadata (i.e., data about data) database schema Data storage and definition language language in which the storage structure and access methods used by the database system are specifiedUsually an extension of the data definition language Database Host Languages C,

36、C+, Fortran, Lisp, COBOLApplication prog.Local VarsDBMSCalls toDBHost language is completely general Query languageless general non procedural andoptimizable(Memory)(Storage)Database Definition A database is a collection of stored operational data used by various applications and/or users by some pa

37、rticular enterprise or by a set of outside authorized applications and authorized usersA DataBase Management System (DBMS) is a software system that manages execution of users applications to access and modify database data so that the data security, data integrity, and data reliability is guarantee

38、d for each application and each application is written with an assumption that it is the only application active in the database.DBMS Architecture Logical and Physical Database ComponentsData Definition Language (DDL)Data Manipulation Language (DML)Host Language InterfaceData AdministratorUsersQuery

39、 ProcessorCompilerOptimizerManagementTransaction ManagerFile ManagerBuffer ManagerAuthorization and Integrity ManagerLogicalPhysicalQuery ProcessorCompiler verifies whether a program or query is written in accordance with DDL and DML rulesOptimizer Finds the most effective way to access the required

40、 data and supply it in a user requested form. Monitors the query execution and modifies a query evaluation plan if necessary.Storage ManagementStorage manager is a program module that provides the interface between the low-level data stored in the database and the application programs and queries su

41、bmitted to the system.The storage manager is responsible to the following tasks: interaction with the file manager efficient storing, retrieving and updating of data Transaction ManagerA transaction is a collection of operations that performs a single logical function in a database applicationTransa

42、ction-management component ensures that the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction failures.Concurrency-control manager controls the interaction among the concurrent transactions, to ensure the cons

43、istency of the database.File ManagerFile Manager is responsible for mapping logical database units (objects, relations, etc.) into a set of low level files.It is responsible for maintenance of files and indexes on them. It should be able to create and destroy index and collect unused storage space t

44、o eliminate an unneeded gaps on disks.Buffer ManagerBuffer Manager is responsible for the allocation and maintenance buffer space in a memory to facilitate processing database data by several concurrent applications.Buffer Manager decides when to load data from a buffer to a database or discard the

45、data and under what conditions a new data should be put into a bufferAuthorization and Integrity ManagerThis manager is responsible for granting an access to database or portions thereof only to authorized users and preventing the access to unauthorized usersIntegrity manager must assure data integr

46、ity during normal database operations as well as during the database failuresData AdministratorCoordinates all the activities of the database system; the database administrator has a good understanding of the enterprises information resources and needs.Database administrators duties include:Schema d

47、efinitionStorage structure and access method definitionSchema and physical organization modificationGranting user authority to access the databaseSpecifying integrity constraintsActing as liaison with usersMonitoring performance and responding to changes in requirementsDatabase UsersNave do not know

48、 about database too much, invoke application programs that are prepared alreadyApplication Programmers know how to interact with the system but may not know how DBMS is designedSophisticated users that know advanced use of the system and can use the system and packages on the top of the systemDBMS s

49、ystem users write specialized database applications that do not fit into the traditional data processing framework A Little Design Methodology: Entity-Relationship ModelThe enterprise data can be described as a set of entities and a set of relationships between them.Entity a data that pertains to, o

50、r describes some component of the enterpriseEach entity is characterized by a set of attributesRelationship describes an interconnection between different entitiesEntity Set a set of entities that are characterized by the same entity definitionRelationship Set a set of relationships of the same type

51、 Entity-Relationship ModelExample of schema in the entity-relationship modelRfOcL9H6E2B+y(u%r#oWlThQeNbJ8G5D1A-x*t$qYnVkSgPdMaI7F3C0z)v&s!pXmUiRfOcK9H6E2B+x(u%rZoWlThQeMbJ8G4D1A-w*t!qYnVjSgPdLaI6F3C0y)v&s#pXlUiRfNcK9H5E2B+x(u$rZoWkThQeMbJ7G4D1z-w*t!qYmVjSgOdLaI6F3B0y)v%s#pXlUiQfNcK8H5E2A+x*u$rZnWkTh

52、PeMaJ7G4C1z-w&t!qYmVjRgOdL9I6F3B0y(v%s#oXlUiQfNbK8H5D2A+x*u$qZnWkShPeMaJ7F4C1z)w&t!pYmUjRgOcL9I6E3B+y(v%r#oXlTiQfNbK8G5D2A-x*u$qZnVkShPdMaJ7F4C0z)w&s!pYmUjRfOcL9H6E3B+y(u%r#oWlTiQeNbJ8G5D1A-x*t$qYnVkSgPdMaI7F3C0z)v&s!pXmUjRfOcK9H6E2B+y(u%rZoWlThQeNbJ8G4D1A-w*t$qYnVjSgPdLaI7F3C0y)v&s#pXmUiRfNcK9H5E2B

53、+x(u$rZoWkThQeMbJ8G4D1z-w*t!qYnVjSgOdLaI6F3C0y)v%s#pXlUiRfNcK8H5E2A+x(u$rZnWkThPeMbJ7G4C1z-w&t!qYmVjRgOdL9I6F3B0y(v%s#oXlUiQfNcK8H5D2A+x*unVjSgOdLaI6F3C0y)v%s#pXlUiRfNcK8H5E2A+x(u$rZnWkThPeMbJ7G4C1z-w&t!qYmVjSgOdL9I6F3B0y)v%s#oXlUiQfNcK8H5D2A+x*u$rZnWkShPeMaJ7G4C1z)w&t!pYmVjRgOcL9I6E3B0y(v%r#oXlTiQf

54、NbK8H5D2A-x*u$qZnWkShPdMaJ7F4C1z)w&s!pYmUjRgOcL9H6E3B+y(v%r#oWlTiQeNbK8G5D1A-x*t$qZnVkSgPdMaI7F4C0z)v&s!pXmUjRfOcL9H6E2B+y(u%r#oWlThQeNbJ8G5D1A-w*t$qYnVkSgPdLaI7F3C0z)v&s#pXmUiRfOcK9H5E2B+x(u%rZoWkThQeMbJ8G4D1A-w*t!qYnVjSgPdLaI6F3C0y)v&s#pXlUiRfNcK9H5E2A+x(u$rZoWkThPeMbJ7G4D1z-w&t!qYmVjSgOdL9I6F3B0y

55、)v%s#oXlUiQfNcK8H5E2A+x*u$rZnWkThPeMaJ7G4C1z-w&t!pYmVjRgOdL9I6E3B0y(v%s#oXlTiQfNbK8H5D2A-x*u$qZnWkShPdMaJ7F4C1z)w&t!pYmUjRgOcL9I6E3B+y(v%r#oXlTiQeNbK8G5D2A-x*t$qZnVkShPdMaI7F4C0z)w&s!pXmUjRfOcL9H6E2B+y(u%r#oWlTiQeNbJ8G5D1A-x*t$qYnVkSgPdMaI7F3C0z)v&s!pXmUiRfOcK9H6E2B+x(u%rZoWlThQeMbJ8G4D1A-w*t!qYnVjS

56、gPdLaI6F3C0y)v&s#pXmUiRfNcK9H5E2B+x(u$rZoWkThQeMbJ3C0z)v&s!pXmUiRfOcK9H6E2B+x(u%rZoWlThQeMbJ8G4D1A-w*t$qYnVjSgPdLaI7F3C0y)v&s#pXmUiRfNcK9H5E2B+x(u$rZoWkThQeMbJ7G4D1z-w*t!qYmVjSgOdLaI6F3B0y)v%s#pXlUiQfNcK8H5E2A+x(u$rZnWkThPeMbJ7G4C1z-w&t!qYmVjRgOdL9I6F3B0y(v%s#oXlUiQfNbK8H5D2A+x*u$qZnWkShPeMaJ7F4C1z)

57、w&t!pYmVjRgOcL9I6E3B0y(v%r#oXlTiQfNbK8G5D2A-x*u$qZnVkShPdMaJ7F4C0z)w&s!pYmUjRfOcL9H6E3B+y(u%r#oWlTiQeNbK8G5D1A-x*t$qZnVkSgPdMaI7F4C0z)v&s!pXmUjRfOcK9H6E2B+y(u%rZoWlThQeNbJ8G4D1A-w*t$qYnVjSgPdLaI7F3C0y)v&s#pXmUiRfOcK9H5E2B+x(u%rZoWkThQeMbJ8G4D1z-w*t!qYnVjSgOdLaI6F3C0y)v%s#pXlUiRfNcK8H5E2A+x(u$rZnWkTh

58、PeMbJ7G4D1z-w&t!qYmVjSgOH5E2B+x(u%rZoWkThQeMbJ8G4D1z-w*t!qYnVjSgOdLaI6F3C0y)v%s#pXlUiRfNcK8H5E2A+x(u$rZoWkThPeMbJ7G4D1z-w&t!qYmVjSgOdL9I6F3B0y)v%s#oXlUiQfNcK8H5D2A+x*u$rZnWkShPeMaJ7G4C1z)w&t!pYmVjRgOdL9I6E3B0y(v%s#oXlTiQfNbK8H5D2A-x*u$qZnWkShPdMaJ7F4C1z)w&s!pYmUjRgOcL9H6E3B+y(v%r#oWlTiQeNbK8G5D2A-x*

59、t$qZnVkShPdMaI7F4C0z)w&s!pXmUjRfOcL9H6E2B+y(u%r#oWlThQeNbJ8G5D1A-w*t$qYnVkSgPdLaI7F3C0z)v&s#pXmUiRfOcK9H6E2B+x(u%rZoWlThQeMbJ8G4D1A-w*t!qYnVjSgPdLaI6F3C0y%r#oWlThQeNbJ8G5D1A-w*t$qYnVkSgPdMaI7F3C0z)v&s!pXmUiRfOcK9H6E2B+x(u%rZoWlThQeMbJ8G4D1A-w*t!qYnVjSgPdLaI6F3C0y)v&s#pXlUiRfNcK9H5E2A+x(u$rZoWkThQeMb

60、J7G4D1z-w*t!qYmVjSgOdLaI6F3B0y)v%s#pXlUiQfNcK8H5E2A+x*u$rZnWkThPeMaJ7G4C1z-w&t!pYmVjRgOdL9I6F3B0y(v%s#oXlUiQfNbK8H5D2A+x*u$qZnWkShPeMaJ7F4C1z)w&t!pYmUjRgOcL9I6E3B+y(v%r#oXlTiQeNbK8G5D2A-x*u$qZnVkShPdMaJ7F4C0z)w&s!pYmUjRfOcL9H6E3B+y(u%r#oWlTiQeNb4C1z)w&t!pYmUjRgOcL9I6E3B+y(v%r#oXlTiQfNbK8G5D2A-x*u$qZ