Write a python code

Module 5: Using Databases with Python

• File-based systems

• Database systems

• Relational databases

• Structured Query Language

ISM 6405- Dr. Milad Baghersad 1

Reference: Database Systems: Design, Implementation and Management, 12th edition by Carlos Coronel and Steven Morris (Cengage Learning, ISBN: 978-1-305-62748-2)

History: traditional file-based systems

ISM 6405- Dr. Milad Baghersad 2

• Computerized manual filing systems

• Individual files for each purpose

• Each business unit had its own file system

• Organized to facilitate the expected use of the data

• Data processing specialist required to retrieve data and run reports

Example: file-based systems

ISM 6405- Dr. Milad Baghersad 3

• Suppose, as a FAU student, that you need to do the following things:

1. Register for courses

2. Pay tuition

3. Work part-time on campus

File Based Systems Illustration

ISM 6405- Dr. Milad Baghersad 4

TuitionRec (ID, name,

address, amtPaid,

balance, …)

RegRec (ID, name,

address, courses, …)

EmpRec (ID, name,

address, wage, …)

Tuition

payment

entry and

reports

File

handling

routines

File

definition

Course

registration

entry and

reports

File

handling

routines

File

definition

Work-study

data entry

and reports

File

handling

routines

File

definition

Tuition payment application programs

Course registration application programs

Work-study application programs

Payment file(s)

Registration file(s)

Work-study file(s)

Issues with file-based systems

ISM 6405- Dr. Milad Baghersad 5

• Data redundancy

• Different files contain same information (ID, name, address, etc…)

• Isolation of data in separate systems

• Data inconsistency (e.g., different ID values for the same person)

• Lack of data integrity

• Data anomalies

• All changes may not be made successfully

• Update / Insertion / Deletion anomalies

Better approach: database systems

• Database: a collection of interrelated data organized in such a way that it corresponds to the

needs and structure of an organization and can be

used by more than one person for more than one

application.

ISM 6405- Dr. Milad Baghersad 6

Example: database system

ISM 6405- Dr. Milad Baghersad 7

Tuition

payment

entry and

reports

Course

registration

entry and

reports

Work-study

data entry

and reports

Tuition payment application programs

Course registration application programs

Work-study application programs

DBMS

Database

Advantages of database systems

ISM 6405- Dr. Milad Baghersad 8

• Minimal data redundancy

• Data consistency

• Integration of data

• Improved data sharing

• Enforcement of standards

• Ease of application development

• Uniform security, privacy, and integrity

• Data independence from applications • "self-describing" data stored in a data dictionary (metadata)

File-based systems often imply "flat" data files:

ISM 6405- Dr. Milad Baghersad 9

RecNo Name Address City State Zip Product Units Amount

1 John Smith 221 Main St. New York NY 08842 Television 1 $500

2 William Chin 43 1st Ave. Redmond WA 98332 Refrigerator 1 $800

3 William Chin 43 1st Ave. Redmond WA 98332 Toaster 1 $80

4 Marta Dieci 2 West Ave. Reno NV 92342 Television 1 $500

5 Marta Dieci 2 West Ave. Reno NV 92342 Radio 1 $40

6 Marta Dieci 2 West Ave. Reno NV 92342 Stereo 1 $200

7 Peter Melinkoff 53 NE Rodeo Miami FL 18332 Computer 1 $1500

8 Martin Sengali 1234 5th St. Boston FL 03423 Television 1 $500

9 Martin Sengali 1234 5th St. Boston FL 03423 Stereo 1 $200

10 Martin Sengali 1234 5th St. Boston FL 03423 Radio 1 $40

11 Martin Sengali 1234 5th St. Boston FL 03423 Refrigerator 1 $80

Subset of the problems with a file-based

system:

ISM 6405- Dr. Milad Baghersad 10

• Redundancy (i.e. data duplication)

• Update anomalies

• Update a single address requires you to update multiple entries

• Insertion anomalies

• You cannot insert information about customer until they have actually purchased something

• Deletion anomalies

• If there is a single instance of a given product being sold, and for some reason we decide to remove the customer who purchased it, we will lose information about the product also

Improving data management: Types of database

models

ISM 6405- Dr. Milad Baghersad 11

• Hierarchical database model

• Tree-based approach developed in 1960's

• Based on parent-child relationships (1:M)

• Network database model

• Created to improve on hierarchical model

• Allows records to have more than one parent

• Can access data from multiple points

• Relational database model

Relational database model

Turing Award Winner, Edgar F. Codd’s landmark paper, “A Relational Model of

Data for Large Shared Data Banks" (1970) laid out a new way to organize and

access data: the Relational Model.

12

Customer(CustomerID, Name, …

Order(OrderID, CustomerID, OrderDate, …

ItemsOrdered(OrderID, ItemID, Quantity, …

Items(ItemID, Description, Price, …

This Photo by Unknown

Author is licensed under

CC BY-SA

Flat-file database

RecNo Name Address City State Zip Product Units Amount

1 John Smith 221 Main St. New York NY 08842 Television 1 $500

2 William Chin 43 1st Ave. Redmond WA 98332 Refrigerator 1 $800

3 William Chin 43 1st Ave. Redmond WA 98332 Toaster 1 $80

4 Marta Dieci 2 West Ave. Reno NV 92342 Television 1 $500

5 Marta Dieci 2 West Ave. Reno NV 92342 Radio 1 $40

6 Marta Dieci 2 West Ave. Reno NV 92342 Stereo 1 $200

7 Peter Melinkoff 53 NE Rodeo Miami FL 18332 Computer 1 $1500

8 Martin Sengali 1234 5th St. Boston FL 03423 Television 1 $500

9 Martin Sengali 1234 5th St. Boston FL 03423 Stereo 1 $200

10 Martin Sengali 1234 5th St. Boston FL 03423 Radio 1 $40

11 Martin Sengali 1234 5th St. Boston FL 03423 Refrigerator 1 $80

13

Example: relational database

14

Customer table

CusNo Name Address City State Zip

1 John Smith 221 Main St. New York NY 08842

2 William Chin 43 First Ave. Redmond WA 98332

3 Marta Dieci 2 West Ave. Reno NV 92342

4 Peter Melinkoff 53 NE Rodeo Miami FL 18332

5 Martin Sengali 1234 5th St. Boston MA 03423

CusNo Product Units Amount

1 Television 1 $500

2 Refrigerator 1 $800

2 Toaster 1 $80

3 Television 1 $500

3 Radio 1 $40

3 Stereo 1 $200

4 Computer 1 $1500

5 Television 1 $500

5 Stereo 1 $200

5 Radio 1 $40

5 Refrigerator 1 $800

OrderItem table

Another database in relational form

ISBN Title PubID Price

0-103-45678-9 Iliad 1 $25.00

0-11-345678-9 Moby Dick 3 $49.00

0-12-333433-3 On Liberty 1 $25.00

0-12-345678-9 Jane Eyre 3 $49.00

0-123-45678-0 Ulysses 2 $34.00

0-321-32132-1 Balloon 3 $34.00

0-55-123456-9 Main Street 3 $22.95

0-555-55555-9 MacBeth 2 $12.00

0-91-045678-5 Hamlet 2 $20.00

0-91-335678-7 Fairie Queene 1 $15.00

0-99-777777-7 King Lear 2 $49.00

0-99-999999-9 Emma 1 $20.00

1-1111-1111-1 C++ 1 $29.95

1-22-233700-0 Visual Basic 1 $25.00

AuID AuName AuPhone

1 Austen 111-111-1111

10 Jones 123-333-3333

11 Snoopy 321-321-2222

12 Grumpy 321-321-0000

13 Sleepy 321-321-1111

2 Melville 222-222-2222

3 Homer 333-333-3333

4 Roman 444-444-4444

5 Shakespeare 555-555-5555

6 Joyce 666-666-6666

7 Spencer 777-777-7777

8 Mill 888-888-8888

9 Smith 123-222-2222

ISBN AuID

0-103-45678-9 3

0-11-345678-9 2

0-12-333433-3 8

0-12-345678-9 1

0-123-45678-0 6

0-321-32132-1 11

0-321-32132-1 12

0-321-32132-1 13

0-55-123456-9 9

0-55-123456-9 10

0-555-55555-9 5

0-91-045678-5 5

0-91-335678-7 7

0-99-777777-7 5

0-99-999999-9 1

1-1111-1111-1 4

1-22-233700-0 4

PubID PubName PubPhone

1 Big House 123-456-7890

2 Alpha Press 999-999-9999

3 Small House 714-000-0000

BOOK AUTHOR

PUBLISHER BOOK/AUTHOR

15

Install MySQL Server and Workbench

Use the video: How To Install MySQL (Server and

Workbench) [05:57]

Please note as you are installing MySql, when you are

selecting Products and Features (see [02:30] in the video),

click on MySQL Connectors and add Python connector also.

If you can not add Python connector, later I will teach you

another method to connect Python and MySQL.

16

Manipulating data in databases

Structured Query Language (SQL)

• creating database and table structures

• performing data manipulation and administration

• querying the database to extract useful information

17

Categories of SQL commands

• Data Definition Language (DDL) – Commands that define a database, including creating, altering, and

dropping tables and stored procedures, and establishing constraints

• CREATE TABLE, set PRIMARY KEY

• Data Manipulation Language (DML) – Commands that are used to manipulate data and extract information

• SELECT, UPDATE, INSERT, DELETE

18

Data types

• ANSI/ISO SQL data types:

– INTEGER / SMALLINT

– DECIMAL(precision, scale)

– CHAR(n) - fixed length character data

– VARCHAR(n) - variable length character data

– DATE - Julian date format

– plus several more…

• Other DBMS add additional data types.

19

MySQL data types (cont.)

Primary date and time types:

• DATE 'YYYY-MM-DD' format

range: '1000-01-01' to '9999-12-31'

• DATETIME 'YYYY-MM-DD HH:MM:SS' format

range: '… 00:00:00' to '… 23:59:59'

Invalid dates and times are converted to zero values: '0000-00-00'

Some built-in functions:

NOW( ), CURDATE( ), DATEDIFF( ), INTERVAL

DATE( ), TIME( ), DAY( ), YEAR( ), MONTH( ), etc.

20

Sample Database

Posted on course site:

• class.sql (MySQL 5.0)

• We will be running examples of queries on this database as

we cover the material, to help illustrate how the different

SQL statements work.

• I strongly recommend working through the examples on your

own as we go, in order to better understand them.

21

Sample Database (continued)

6 Tables:

22

StackOverflow- Mocking data for Angular2/typescript with my own

model type properties Licensed by Stack Exchange Network

GROUP BY

Ex: For each Dept_Code, list:

The number of employees (as count) and the total credit limit (as

total)

23