For October 19 / Quiz 2: study Syllabus:
Study resources: Course PowerPoint Workbooks
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.
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. 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.
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.