Databases are books, demographic records, student database - PDF document

Course Book "Database Systems: The Complete Book, 2E", Databases TDA357/DIT620 by Hector Garcia-Molina, Jeffrey D. Ullman, and Jennifer Widom Graham Kemp Approx. chapters 1-12 kemp@chalmers.se Room 6475, EDIT Building Learning outcomes (“goals”) Examination • Written exam • Discuss and use features of different data models: the entity- – Tuesday 13 January 2015, 14:00-18:00 (but check Student Portal) relationship model, the relational model and the semi-structured – 60 points (3/4/5 = 24/36/48, G/VG = 24/42) model. • Apply design theory for relational databases. • Four assignments to be submitted • Describe the effect of indexes and transactions in a relational – we recommend that you work in pairs – work must be submitted via the ’fire’ system database. – obtain Oracle username and password via ’fire’ system • Describe how access can be controlled via user authorisation. • Implement a database design using a data definition language. • Query and modify data using a data manipulation language. Course Web Page • Express queries in relational algebra. • Implement a database application in a host language. http://www.cse.chalmers.se/edu/course/TDA357/ • Construct an entity-relationship diagram for a given domain. • Design and implement a database application that meets given requirements. A database is … Why a whole course in Databases? • a collection of data Banking, ticket reservations, customer records, sales records, product records, inventories, employee records, address • managed by specialised software called a Databases are books, demographic records, student database management system (DBMS) records, course plans, schedules, (or, informally, a “database system”) everywhere! surveys, test suites, research data, genome bank, medicinal records, time tables, news archives, sports results, e- • needed for large amounts of persistent, commerce, user authentication systems, structured, reliable and shared data web forums, www.imdb.com, the world wide web, … 1

Examples File-oriented information system • Banking – Drove the development of DBMS • Industry – Inventories, personnel records, sales … Customer Payroll Employee Inventory Sales – Production Control records records records records records – Test data • Research – Sensor data – Geographical data Customer Payroll Personnel Purchasing Marketing service – Laboratory information management systems department department department department department – Biological data (e.g. genome data) Database-oriented information Problems with working with files system • Redundancy Customer Marketing – Updates service department department – Wasted space Integrated • Changing a data format will require all database application programs that read/write these Payroll Purchasing files to be changed. department department • Sharing information between departments Personnel department can be difficult. Typical Computer System Centralised control of data • amount of redundancy can be reduced User interface Application programs Peripherical unit (e.g. Sensor) – less inconsistency in the stored data Other system • stored data can be shared TCP/IP • standards can be enforced SQL • security restrictions can be applied • data integrity can be maintained Database – validation done in one place • conflicting requirements can be balanced SQL • provides data independence – can change storage structure without affecting (html/java/xml/...) applications 2

Motivation for database systems Traditional File Structures Needed for large amounts of persistent, structured, reliable and shared data (Ted Codd, 1973) A short digression … • Large amounts: – needs indexing for fast access – needs a load utility • Persistent: – needs schema definition of types which evolves • Structured: – storage schema held with data – query language (e.g. SQL) independent of storage • Shared: – locking mechanism for concurrent update – access control via DBMS – centralised integrity checking • Reliable: – changes to disc pages are logged – commit protects against program of disc crash – can undo (rollback) uncommitted updates UNIX file management Actual organisation is hidden • Just as the file management system in an operating system gives the users the illusion that a text file is stored on disc as a long consecutive sequence of characters … • … a database management system gives the users the illusion that their data are stored on disc in accordance with a data model . Data models Why not a file system? • Storing data in a computer system File systems are requires describing the data according to • Structured some data model , in a form which can be • Persistant represented directly within the computer. • Changable • A data model specifies the rules • Digital according to which data are structured and also the associated operations that are permitted. … but oh so inefficient! 3

Data models: brief overview Database Management Systems • “No data model” • Hierarchical databases: – Flat files – ”Easy” to design if only one hierarchy – Efficient access • “Classical” data models – Low-level view of stored data – Hierarchical (tree) – Hard to write queries – Network (e.g. CODASYL) (graph) • Network databases: – Relational (Codd, 1970) (tables) – ”Easy” to design • Semantic data models, e.g. – Efficient access – Entity-Relationship model (Chen, 1976) – Low-level view of stored data – Functional Data Model (Shipman, 1981) – Very hard to write queries – SDM (Hammer and McLeod, 1981) Database Management Systems Relational DBMSs Relational databases: • Very simple model – Hard to design • Familiar tabular structure – Use specialized storage techniques – Efficient access • Has a good theoretical foundation from – Provides high-level views of stored data mathematics (set theory) based on mathematical concepts – Easy to write queries • Industrial strength implementations, e.g. – Not all data fit naturally into a tabular structure • Other databases: – Oracle, Sybase, MySQL, PostgreSQL, – Some based on a semantic data models Microsoft SQL Server, DB2 (IBM mainframes) – Object-oriented database management systems • Large user community (OODBMS) – “NoSQL” (“not only SQL”) Database system architecture Data Definition Language “A language that allows the DBA [database administrator] or user to describe and name the entities, attributes and relationships required for the application, together with any associated integrity or security constraints.” [Definition from Connolly and Begg (2002) Database Systems: A Practical Approach to Design Implementation and Management. Third Edition. Addison Wesley.] DDL statements are compiled into metadata (“data about data”). 4

Data Manipulation Language Database system studies “A language that provides a set of operations to support the basic data 1. Design of databases, e.g. manipulation operations on data held in the database.” – Entity-Relationship modelling – relational data model [Definition from Connolly and Begg (2002) Database Systems: A Practical Approach to Design, Implementation and Management. Third Edition. Addison Wesley.] – dependencies and normalisation – XML and its data model Data manipulation operations include: 2. Database programming, e.g. • inserting new data into the database; – relational algebra • modifying data stored in the database; – data manipulation and querying in SQL • deleting data from the database; – application programs • retrieving data from the database. – querying XML 3. Database implementation, e.g. The part of the DML involved with data retrieval is called the – indexes, transaction management, concurrency control, query language . recovery, etc. Course Objectives Course Objectives – Design When the course is through, you should Design – Given a domain, know how to design a Construction database that correctly models the domain and its constraints ”We want a database that we can use for scheduling courses and lectures. This is Applications Usage how it’s supposed to work: …” Course Objectives – Design Course Objectives – Construction • Entity-relationship (E-R) diagrams When the course is through, you should • Functional Dependencies – Given a database schema with related • Normal Forms constraints, implement the database in a relational DBMS code day hour roomNr name Courses(code, name, dept, examiner) name Course Of Lecture In Room Rooms(roomNr, name, building) dept Lectures(roomNr, day, hour, course) building roomNr -> Rooms.roomNr responsible course -> Courses.code 5