DBMS CSC351621 Getting Started and Quick Overview Motivation SQL is one of the significant skills required for Data Science According to Glassdoor the average salary for SQL Developer is ID: 812847
Download The PPT/PDF document "Database Management Systems" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Database Management Systems
DBMS | CSC-351/621
Slide2Getting Started and Quick Overview
Slide3Motivation
SQL is one of the significant skills required for
Data Science
According to Glassdoor, the average salary for SQL Developer is
$82k per year
in the United States
Average Salary for Data Scientist
$113,436
(110k in Philadelphia
)
According to Glassdoor, the average salary for PHP Developer is
$82k per year
According to TIOBE, the software quality company, the SQL Language is one of the highest positions (top 10) (since 2002) – August 2020
According to TIOBE, the software quality company, the PHP Programming Language is one of the highest positions (top 10) (since 2002) – August 2020
Slide4Title and content layout with chart
And it continues to rise -
https://www.tiobe.com/tiobe-index/
Slide5Course Overview
Relational Databases (since 1970)
Entity Relationship Diagram (ERD) / Database Modeling
SQL: CRUD queries, views, procedures, and triggers
Cloud Computing – SQL Server
Database Management Systems (DBMS)
Web Applications: HTML, CSS, and PHP
NoSQL: MongoDB – document-oriented database program
Slide6Course Goals and Objectives
To develop an understanding of Relational Databases (SQL, and ERD Modelling)
To develop an understanding of advanced concepts of Relational Databases
To develop an understanding of NoSQL Databases
Basic structure of web application and how a web browser interacts with a web server. Request-Response Cycle that is the basis of the Hypertext Text Transfer Protocol (HTTP)
Final project – Web App that integrates with DB server
Slide7What is a Database?
Slide8What is a Database?
A collection of individual data items
Stored in a highly structured way
Represents a model of reality
Organizes the data in a way that facilitates efficient access to the information captured in the data
Data can be stored in a single location
Or, distributed across large networks
Slide9CRUD Tasks
Create
Read
Update
Delete
All performed by a database engine
Slide10What is Database Management Systems?
Slide11Simplified - DBMS
A database management system (DBMS) is a system software for creating and managing databases
The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data
Happy Coding!
Slide12Database Management Systems (DBMS)
Server-based systems such as SQL Server or Oracle
A system that communicates with the database server to perform CRUD operations
Creation of databases
Insertion, storage, retrieval, update, and deletion of the data stored in the database
Maintenance of databases (i.e., backup, user permissions/control)
Slide13Why Database Management Systems (DBMS)?
Data independence and efficient access.
Reduced application development time.
Data integrity and security.
Uniform data administration.
Concurrent access, recovery from crashes.
Slide14Slide15Relational Database Management Systems
Being specific about what database management system we are using
Database systems may vary, from relational (i.e., MySQL, T-SQL) to document-oriented databases such as MongoDB
Slide16System Categories
Major server DBMS vendors
Microsoft SQL Server (MSSQL)
Oracle
DB2 (IBM)
PostgreSQL (open source, free to install and use)
MySQL (open source, free to install and use)
Slide17What is SQL?
Structured
Query
Language
Used by the DBMS to interact with the database
Create new tables
Insert and update data
Retrieve information
A very beautiful and simple language!
Slide18Initial Terminology
Data
– facts that are recorded and can be retrieved/accessed
Data formats – text, numbers, figures, audio/video, recordings and more
Data is saved and kept because it is considered to be of use to an intended user
Big Data?
Slide19Initial Terminology
Metadata –
data that describes the structure and prosperities of the data
Metadata is essential for the proper understanding and use of the data
Data without metadata
Slide20Initial Terminology
Data with metadata
Slide21Initial Terminology
Database metadata
– represents the structure of the database
Database content that is not the data itself (data about the data)
Contains:
Names of the data structures
Data types
Data descriptions
Other information describing the characteristics of the data
Slide22Initial Terminology
Typical database system architecture
Slide23Initial Terminology
Front-end application
– provide a mechanism for easy interaction between the user and the DBMS
* DML (Data Manipulation Language)
Manipulate data itself
it is used to retrieve, modify/update, delete, and insert
* DDL (Data Definition Language)
It is used to define data structures (e.g., create table, alter table)
In other words, it is used to create and modify the structure of database objects in the database (design)
We will talk about that in more details later in this course…
Slide24Initial Terminology
Queries:
access different parts of data and formulate the results of a request
Transactions:
symbolizes a unit of work performed within a system against a databases
A single unit of logic or work
SQL select statement and its result – Wikipedia
Slide25Steps in the Development of Database Systems
Slide26Steps in the Development of Database Systems
Requirements collection, definition, and visualization
Results in the requirements specifying which data the future database system will hold and what the capabilities and functionalities of the database system will be
The
collected
requirements should be clearly
defined
and stated in a written document, and then
visualized
Conceptual database model –
a visualization of requirements by using a conceptual data modeling technique (such as entity-relationship diagram (ERD))
Slide27Steps in the Development of Database Systems
Iterative nature of the database requirements collection, definition, and visualization process
Slide28Steps in the Development of Database Systems
Database modeling (logical database modeling)
– creation of the database model that is implementable by the DBMS
Logical database modelling follows conceptual database modelling
Database implementation
– using a DBMS to implement the database model as an actual database
Most modern databases are implemented using a relational DBMS (RDBMS) software
Slide29Steps in the Development of Database Systems
Developing front-end applications
– designing and creating application for indirect use by the end-users
Front-end applications are based on the database model and the requirements specifying the front-end functionalities
Front-end applications contain interfaces (such as forms and reports) accessible via a navigation mechanism (such as menu/navigation bar)
Slide30Steps in the Development of Database Systems
Database deployment
– releasing the database system for use by the end users
Database use
– the insertion, modification, deletion, and retrieval of the data in the database system
Slide31Steps in the Development of Database Systems
Database administration and maintenance
– performing activities that support the database end user, including dealing with technical issues, such as:
Providing security for the information contained in the database
Ensuring sufficient hard/solid drive space for the database content
Implementing the backup and recovery procedures
Slide32People Involved w/ DB Systems
Database analysts, designers, and developers
Database admins –
perform the tasks related to the maintenance and administration of a database system
Database engineers/designers (
a.k.a
architects) –
involved in the database modeling stage
Database developers –
in charge of implementing the database model as a functioning database using the DBMS software
Database developers are involved in SQL Programming language
Write SQL queries, procedures, triggers, and so on
Slide33Additional DBMS Functionality
DBMS may additionally provide
Protection or security measure to prevent unauthorized access
“Active” processing to take internal actions on data
Maintenance of the
db
and associated programs
Slide34Demo
/
IPInfoDB
/
iplocation.php
Slide35Somebody, Please, Bring Some Order to This Madness
Slide36Somebody, Please, Bring Some Order to This Madness – Cont’d
NoSQL Databases
Slide37Relational Data
Tables or Relations
Slide38Relational Database: Schemas
Slide39Key-Value Store
Implemented as an associative array, map, symbol table, or dictionary abstract data type composed of a collection of (
key
,
value
) pairs such that each possible key appears at most once in the collection.
A simple
put
/
get
interface
Great properties: scalability, availability, reliability
Slide40Key-Value Store Usage Scenarios
Increasingly popular within data centers and in P2P
Data center
P2P
Dynamo
amazon.com
Voldemort
LinkedIn
Cassandra
Facebook
Vuze DHT
Vuze
uTorrent DHT
uTorrent
Slide41Row Store and Column Store
In row store data are stored in the disk tuple by tuple.
Where in column store data are stored in the disk column by column.
Column-stores are more I/O efficient for read-only queries as they read only those attributes which are accessed by a query.
Source: Column-Oriented Database Systems, VLDB 2009. Tutorial;
S.
Harizopoulos
, D.
Abadi
, P.
Boncz
Slide42Row Store and Column Store
So column stores are suitable for
read-mostly, read-intensive, large data repositories
Row Store
Column Store
(+) Easy to add/modify a record
(+) Only need to read in relevant data
(-) Might read in unnecessary data
(-)
Tuple
writes require multiple accesses
Slide43Program Flow
Ecological Network
Biological Network
Social Network
Chemical Network
Web Graph
Graph Databases
Slide44Graph Databases: Query
Find all the restaurants my friends (in Facebook) like
Slide45So, Why Study Relational DBs?
Jack Clark, The Register, 30 August 2013
: “The tech world is turning back toward SQL, bringing to a close a possibly misspent half-decade in which startups courted developers with promises of infinite scalability and the finest imitation-Google tools available, and companies found themselves exposed to unstable data and poor guarantees.”
Google Spanner paper, October 2012
: “We believe it is better to have application programmers deal with performance problems due to overuse of transactions as bottlenecks arise, rather than always coding around the lack of transactions.”
Sean Doherty in Wired, September 2013
: “But don’t become unnecessarily distracted by the shiny, new-fangled, NoSQL red buttons just yet. Relational databases may not be hot or sexy but for your important data there is no substitute.”
Slide46And, The Key Reason of All
Gartner
estimates RDBMS market at
$26B
with about 9% annual growth, whereas
Market Research Media Ltd
expects NoSQL market to be at
$3.5B
by 2018.
Source: C Mohan’s tutorial
Can someone check it!
Slide47Summary
DBMS used to maintain, query large datasets.
Benefits include recovery from system crashes, concurrent access, quick application development, data integrity and security.
Levels of abstraction give data independence.
A DBMS typically has a layered architecture.
DBAs hold responsible jobs and are
well-paid
!
DBMS R&D is one of the broadest, most exciting areas in CS.