Explore

f23 IN3193 Database Class Plan

⁠

For October 19 / Quiz 2: study Syllabus:

Study resources: Course PowerPoint Workbooks

⁠

https://coda.io/@peter-sigurdson/sqldatabase⁠

⁠

https://www.dropbox.com/scl/fi/nm1jo8pdliw7pzogje326/Peter-s-Book-on-Databases.pptx?rlkey=i6twnwhj519fjur2j29auexot&dl=0⁠

⁠

https://www.dropbox.com/scl/fi/xi8nl9kcahd53kmzcyzbs/Structured-and-Big-Data-Databases.pptx?rlkey=n2zwmi732ffhwoiu7ekyws30e&dl=0⁠

⁠

Codd’s Laws: The basis of SQL

What is SQL? A programming language that runs on the engine of a database server to create databases and to obtain

3 flavors of SQL: DQL : Data Query Language: Read operations (R in CRUD): inner joins, outer joins, left and right joins; Filtering and Aggregation functions

DML : Data Manipulation Language : Create, Update, Delete parts of CRUD

DDL : Data Definition Language : create and delete databases, tables, fields

DCL : Data Control Language / security: allow/deny access to tables (we are not doing anything with this)

Primary keys, foreign Keys

Predicate joins between tables

How to use SQL to build the database to run the company:

Study your Business Domain (= all the data and processes that run your Business)

Identify each TOPIC in the Business Domain: name it and create tables for each ENTITY in the Business Domain.

Study each ENTITY, list all the attributes of that Entity: these become the fields of your Table. Table consists of ROWSETS: Each rowset is the set of attributes to describe one data record in your Business Domain. Each rowset (= RECORD) must have a Primary Key to uniquely identify it. This Primary Key Must Have a one and only relationship with each field in the rowset. Each rowset must have a Unique Primary Key that MUST NOT be duplicated in the Table: This is called the First Normal Form NF1. THIS IS THE REASON WHY WE MUST HAVE A RELATOR TABLE WHENEVER WE HAVE A MANY:MANY RELATIONSHIP. A student can take Many Classes. A class has Many Students.

October 11/12: Start Assignment 2: Building the College Enrollment System using MySQL

October 4: What have we done so far:

Studied the Questions: <questions for OCT 19 test>

What is a Relational Database?

The relational database is the biggest single business computer technology in the world.

Businesses run on Data:

The Database Administrator builds and maintains databases: We did data modelling with sqlite and made the college enrollment system.

Informatics Reporting Specialist: Create reports for senior management: We used Power BI which is an Informatics Reporting tool to generate reports on data Excel sheets I provided on Real Estate Data.

Tool Sets:

SQLite and dbbrowser for SQLite: We built the data model for the college enrollment system. The 2 use cases of our data model : (1) Enroll students into Class, and (2) Generate Student Schedules.

PowerBI. Excel.

Microsoft SQL Server: Not supported for Mac OS. In future classes, I will provide a VM ISO that you can run under Oracle Virtual Box to get MS Sql Server.

October 4: Install MySql

Windows:

https://coda.io/@peter-sigurdson/student-lab-workbook-installing-mysql-server-and-workbench-windo⁠

⁠

Mac:

⁠

https://coda.io/@peter-sigurdson/mysqlmacos⁠

⁠

Tools we will be using in Future Classes:

(1) Power Query

(2) Python: Data reports which are highly specific and not supported by SQL’s build in commands.

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.