CIS 5357 Accessing Data from a Database DBMS

CIS 5357 Accessing Data from a Database

DBMS: Database Management System Database A collection of data stored in a standardized format, designed to be shared by multiple users. Database Management System Software that defines a database, manages stored data, supports a query language to manipulate stored data, and provides interfaces to generate reports and connect to applications.

RELATIONAL DATABASE MANAGEMENT SYSTEMS (RDMS) Database – An integrated collection of data These collections of data can be stored in a variety of formats or structures. We’ll focus on storage format known as Relational Databases, where data are stored in one or more tables With relationships between tables.

DEFINITIONS – RDMS § § Relational database: A collection of interrelated tables, with each table representing data about an entity – person, place, object, event, etc. Table: Data about an entity are organized in a collection of rows and columns: § § Each column describes an attribute, characteristic, or a property of the entity. Each row describes one instance of that entity. Often referred to as RECORD Primary Key: § § Rows/Objects Records Each table must have a way to uniquely identify each record in a table. The smallest set of columns that uniquely identifies each row is PRIMARY KEY Primary keys can span more than one column (concatenated keys) We often create a primary key to insure uniqueness (e. g. , Customer. ID, Product#, . . . ) called a surrogate key. Primary key Employee Properties Employee. ID Taxpayer. ID Last. Name First. Name Home. Phone Address 12512 888 -22 -5552 Cartom Abdul(603) 323 -9893 252 South Street 15293 222 -55 -3737 Venetiaan Roland (804) 888 -6667 937 Paramaribo Lane 22343 293 -87 -4343 Johnson John (703) 222 -9384 234 Main Street 29387 837 -36 -2933 Stenheim Susan (410) 330 -9837 8934 W. Maple

THE PRODUCTS TABLE Primary Key Record Columns = Attributes

RELATING TABLES: Invoice and Line. Items Primary Key Foreign Key Records in the Invoice table are related with records in the Line. Item table via the Invoice. ID values. Extract related data from two tables, we will have to JOIN the two tables via the Invoice. ID field.

STRUCTURED QUERY LANGUAGE (SQL) 1. How do you access data stored in these tables? 2. Every DBMS provides a query language that follows a commonly accepted way of accessing and manipulating data in databases. 3. Structured Query Language (SQL or ‘sequel’) 4. SQL includes commands to access/retrieve data, add new data, delete and modify existing data. 5. It even includes commands to define new databases and tables it will contain. 6. CIS 5357 will only focus on the accessing or retrieving data from existing relational databases.

CLASS DEMO – QUERIES TO RETRIEVE DATA 1. Every DBMS makes a user-interface available to interactively work with databases. 2. We’ll use the SQLite DBMS, which is comes bundled in Python installation. 3. However, it does not provide any interface. SQLite relies on User Interfaces provided by 3 rd parties. 4. DB Browser for SQLite is a free User Interface available to work with SQLite databases interactively.

PROCESS FOR ACCESSING DATABASE Connection Make Connection Movies Request Execute Query Python Program Ask for a cursor Pass the query To execute Cursor Populate Cursor Fetch data From Cursor Into Python Retrieve data Resultset

CLASS DEMO – MOVIES DATABASE 1. How to query data from a single table? a. List all categories b. List all categories in reverse alpha order c. List all movies and show movie ID, movie title, release year, & running time. d. List all movies with a running time of less than 80 minutes; 90 minutes e. List all movies with a specific title. f. List all movies released after year 2000. List the movies in alpha order by name; by year in descending order; in descending order by year but alpha order by title g. List all movies with the word ‘Python’ in its title. h. List all movies with name that contains a letter ‘W’ somewhere in the title. i. List the count of movies, average running time, maximum running time, and minimum running time. 2. How to query data from multiple tables? a. List all movies and their associated movie categories b. List all historical movies?