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 View Controller User Updates Manipulates Sees Sees Uses

• Prof. Pfaff.

DBMS Howto

Model-View-Controller (MVC)

Model View Controller User Updates Manipulates Sees Sees Uses User Interface front-end back-end

Database

• 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

• f 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

stars movies studios starin

• wns

name

mother father

born id

founded

name

founder

id

website

title id year

studioId length

cost

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

• rder

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