What’s a database Databases TDA357/DIT620 anyway? Niklas Broberg niklas.broberg@chalmers.se 1 2 A database is … DBMS • Structured Database • Persistant == • Changable Data collection managed by a • Digital specialized software called a Database Management System (DBMS) • True to integrity constraints 3 4 Why a whole course in Databases? Examples • Banking Banking, ticket reservations, customer – Drove the development of DBMS records, sales records, product records, • Industry inventories, employee records, address – Inventories, personnel records, sales … Databases are books, demographic records, student – Production Control records, course plans, schedules, – Test data surveys, test suites, research data, • Research everywhere! – Sensor data genome bank, medicinal records, time – Geographical data tables, news archives, sports results, e- – Laboratory information management systems commerce, user authentication systems, – Biological data (e.g. genome data) web forums, www.imdb.com, the world wide web, … 5 6 1
Why not a file system? Modern DBMS File systems are • Handle persistent data • Structured • Give efficient access to huge amounts of data • Persistant • Give a convenient interface to users • Changable • Guarantee integrity constraints • Digital • Handle transactions and concurrency … but oh so inefficient! 7 8 Database Management Systems Database Management Systems • Relational databases: • Hierarchical databases: – Hard to design – ”Easy” to design if only one hierarchy – Use specialized storage techniques – Efficient access – Efficient access – Low-level view of stored data – Provides high-level views of stored data – Hard to write queries based on mathematical concepts • Network databases: – Easy to write queries – Not all data fit naturally into a tabular structure – ”Easy” to design • Other databases (”NoSQL”): – Efficient access – Some based on semantic data models – Low-level view of stored data – Object-oriented database management systems – Very hard to write queries (OODBMS) – XML-based, Key-value based, … 9 10 Relational DBMSs Database system studies 1. Design of databases, e.g. • Very simple model – Entity-Relationship modelling • Familiar tabular structure – relational data model – dependencies and normalisation • Has a good theoretical foundation from – XML and its data model mathematics (set theory) 2. Database programming, e.g. – relational algebra • Industrial strength implementations, e.g. – data manipulation and querying in SQL – application programs – Oracle, Sybase, MySQL, PostgreSQL, – querying XML Microsoft SQL Server, DB2 (IBM mainframes) 3. Database implementation, e.g. • Large user community – indexes, transaction management, concurrency control, recovery, etc. 11 12 2
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 how it’s supposed to work: …” Usage 13 14 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 15 16 Course Objectives – Construction Course Objectives – Usage When the course is through, you should • SQL Data Definition Language (DDL) – Know how to query a database for relevant CREATE TABLE Lectures ( data using SQL lectureId INT PRIMARY KEY, roomId REFERENCES Rooms(roomId), – Know how to change the contents of a day INT check (day BETWEEN 1 AND 7), database using SQL hour INT check (hour BETWEEN 0 AND 23), course REFERENCES Courses(code), UNIQUE (roomId, day, hour) ”Add a course ’Databases’ with course code ’TDA357’, ); given by …” ”Give me all info about the course ’TDA357’” 17 18 3
Course Objectives – Usage Course Objectives – Applications • SQL Data Manipulation Language (DML) When the course is through, you should INSERT INTO Courses VALUES – Know how to connect to and use a database (’TDA357’, ’Databases’,’CS’, ’Niklas Broberg’); from external applications • Querying with SQL ”We want a GUI application for booking rooms for lectures …” SELECT * FROM Courses WHERE code = ’TDA357’; 19 20 Course Objectives – Applications Course Objectives - Summary • JDBC You will learn how to • design a database // Assemble the SQL command for inserting the • construct a database from a schema // newly booked lecture. • use a database through queries and String myInsert = ”INSERT INTO Lectures ” + ”VALUES (” + room + ”, ” updates + day + ”, ” + hour + ”, ” + course + ”)”; • use a database from an external // Execute the SQL command on the database application Statement stmt = myDbConn.createStatement(); stmt.executeUpdate(myInsert); 21 22 Examination Non-standard Exam Structure • Written exam: Mar 14 (Fri) 8:30-12:30 • Each block will have two or three sections: A, B and possibly C. Everyone is expected to know – 60 points (3/4/5 = 24/36/48, G/VG = 24/42) the A questions, while B and C questions are – Divided into 7 distinct blocks: intended for those seeking higher grades. • E-R diagrams (12) • FDs and Normal Forms (12) • You can only get points from one section within • SQL DDL (8) each block! • Relational Algebra (6) – Less time spent on blocks that you know well. • SQL (8) – Harder to get ”stray” points. • Transactions (6) – A’s give ~30 • XML (8) 23 24 4
Exam – E-R diagrams (12) Exam – FDs and NFs (12) ”A small train company wants to design a booking system ”A car rental company has the following, not very for their customers. …” successful, database. They want your help to improve it. …” • Given the problem description above, construct an E-R diagram. • Identify all functional dependencies you expect to hold in the domain. • Translate an E-R diagram into a database schema. • Indicate which of those dependencies violate BCNF with respect to the relations in the database. • Do a complete decomposition of the database so that the resulting relations are in BCNF. 25 26 Exam – SQL DDL (8) Exam – SQL (8) ”A grocery store wants a database to store information ”The grocery store wants your help in getting proper about products and suppliers. After studying their information from their database. …” domain you have come up with the following database schema. …” • Write a query that finds the total value of the entire inventory of the store. • Write SQL statements that create the relations as • List all products with their current price, i.e. the tables in a DBMS. discount price where such exists, otherwise the base • Write a trigger that, whenever the quantity in store of price. an item drops below a given threshold, inserts an order for that product with the supplier. 27 28 Exam – Relational Algebra (6) Exam – Transactions (6) ”Here is a schema for a database over persons and their ”Here are some transactions that run in parallel. …” employments. …” • What will the end results given by the transactions be? • What does this relational-algebraic expression • What could happen if they were not run as compute? … transactions? • (Write a relational-algebraic expression that computes … .) 29 30 5
Recommend
More recommend
