Database Management Systems (DBMS) Prof. Pfaff. Lafayette College - PowerPoint PPT Presentation

Database Management Systems (DBMS) Prof. Pfaff. Lafayette College February 19, 2018 Prof. Pfaff. DBMS Howto

Model-View-Controller (MVC) Model Updates Manipulates View Controller Sees Sees Uses User Prof. Pfaff. DBMS Howto

Model-View-Controller (MVC) Database Model Updates back-end Manipulates front-end User View Controller Interface Sees Sees Uses User Prof. Pfaff. DBMS Howto

Database Management System Database Management Systems are large software systems that support the storage of data in a predefined format. Database Management Systems are ACID: Atomic – requires that each transaction be ”all or nothing”: if one part of the transaction fails, then the entire transaction fails, and the database state is left unchanged. Consistency – property ensures that any transaction will bring the database from one valid state to another. Isolation – property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed sequentially, i.e., one after the other. Durability – property ensures that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. Databases provide data consistency. Designing for a database implementation, allows you to think about the organization of data in your application. Prof. Pfaff. DBMS Howto

Database A database is an organization of data that is structured as a set of relations , with each relation being seen as a 2D table of data. Columns in the table are called attributes and the rows in the relation are called tuples . title year length genre Star Wars 1977 124 scifi Train Spotting 2017 95 self-help Fantasia 1940 124 animation The table is defined by a schema : movies(title, year, length, genre) Prof. Pfaff. DBMS Howto

Database Schema and Domains The table is defined by a schema : movies(title, year, length, genre) Each attribute will have a data type called the domain : movies(title:string, year:integer, length:integer, genre:text) Prof. Pfaff. DBMS Howto

Database order and keys Any given table will have an undefined order. For an actual implementation, multiple pulls from a table will provide the same ordering, but this can not be relied on. Order is specified by establishing keys, or can be specified with the various SQL commands. movies(title:string, year:integer, length:integer, genre:text) Prof. Pfaff. DBMS Howto

Example Database Here is the schema for a movie database. movies(id, title, year, length, studioId, cost) stars(id, name, born, father, mother) studios(id, name, founded, founder, website) starin(movieId, starId) Prof. Pfaff. DBMS Howto

Entity-Relationship Diagram name id father id mother stars title starin born year movies length id owns studioId studios name cost founded founder website The ER Diagram shows the relationships between the different database entities (relations.) movies(id, title, year, length, studioId, cost) stars(id, name, born, father, mother) studios(id, name, founded, founder, website) starin(movieId, starId) Prof. Pfaff. DBMS Howto

For next class. Create a database for tracking your courses over the four years of your Lafayette career. Create both an ER diagram and the schema for your database. There will be a submission on the lecture Moodle site. Requirements: Do not google any of these ideas. Use the few slides provided here as the key tools for constructing your hypothetical database. The answer is not out there, it is with in yourself. Next time we will talk about what is a good database design. Prof. Pfaff. DBMS Howto

Creating Database Tables Tables can be added to the database, allowing for a give relation. movies(id, title, year, length, studioId, cost) create table movies ( id INT NOT NULL UNIQUE, title CHAR NOT NULL, year INT NOT NULL, length INT NOT NULL, studioId INT NOT NULL, cost INT NOT NULL ); Prof. Pfaff. DBMS Howto

Creating Database Tables Tables can be added to the database, allowing for a give relation. stars(id, name, born, father, mother) create table stars ( id INT NOT NULL UNIQUE, name CHAR NOT NULL, born INT NOT NULL, father CHAR NOT NULL, mother CHAR NOT NULL ); Prof. Pfaff. DBMS Howto

Creating Database Tables Tables can be added to the database, allowing for a give relation. studios(id, name, founded, founder, website) create table studios ( id INT NOT NULL UNIQUE, name CHAR NOT NULL, founded INT NOT NULL, founder INT NOT NULL, website CHAR NOT NULL ); Prof. Pfaff. DBMS Howto

Creating Database Tables Tables can be added to the database, allowing for a give relation. starin(movieId, starId) create table starIn ( movieId INT NOT NULL, starId INT NOT NULL ); Prof. Pfaff. DBMS Howto

Dropping Database Tables A table drop will remove the relation from the database. movies(id, title, year, length, studioId, cost) stars(id, name, born, father, mother) studios(id, name, founded, founder, website) starin(movieId, starId) drop table if exists movies; drop table if exists stars; drop table if exists starIn; drop table if exists studios; Prof. Pfaff. DBMS Howto

Selecting data from tables The select state allows for data to be retrieved from a data in a specified order at different levels of aggregation. movies(id, title, year, length, studioId, cost) select * from movies where cost < 100 order by title ; Prof. Pfaff. DBMS Howto

Data Design Databases and the data structure of your group project can be organized with a few simple tools. sqlite a very simple CLI database implementation. csv files allow easy proto-typing of tables. Makefile allows commands for creation, population, selection, and destruction to be simply automated. Prof. Pfaff. DBMS Howto