SC1-mysql1-2019-da.pdf - SC1 Basic SQL 1\u2028 KEA BEIT 2nd sem 2019 1 SC agenda for this semester \u2022 Basic SQL 1 Intro to databases Intro to MySQL

SC1-mysql1-2019-da.pdf - SC1 Basic SQL 1
 KEA BEIT 2nd...

This preview shows page 1 - 16 out of 75 pages.

SC1: Basic SQL 1 KEA, BEIT 2nd sem, 2019 1
Image of page 1
SC agenda for this semester Basic SQL 1: Intro to databases. Intro to MySQL Workbench. CRUD: Create, Read, Update, Delete (Lenka) Basic SQL 2: Aggregate functions, relations, foreign keys (Jenr) Basic SQL 3: Transactions, normalisation, alter table (Jenr) Triggers and procedures (Lenka) SQL and Interfaces: Client and servers, connect python with MySQL. (Lenka/Jenr) SQL and Interfaces 2: MySQL/Python continued and recap (Lenka) 2
Image of page 2
Literature Murach’s MySQL (main book) —> 24x7 (free books for KEA): 3
Image of page 3
Basic SQL - useful websites 4
Image of page 4
Agenda for today Part 1: Intro to databases, SQL and MySQL Workbench Result: installed software Part 2: Create database and tables, insert data, datatypes Result: First Database Part 3 Primary keys, read from tables, sort data Result: More practice in databases and tables Part 4: Multiple tables and foreign keys Result: Intro to relations between tables in same database 5
Image of page 5
PART 1 Intro to databases and SQL MySQL Workbench 6
Image of page 6
Intro to Databases 7
Image of page 7
What is a database? Organised collection of saved data Relational databases are the most common NoSQL and flat files (JSON) also for other purposes (not part of this course) Dynamic vs. Static Websites 8
Image of page 8
Image of page 9
Databases and SQL DBMS: DataBase Management System - a way of storing data SQL: Structured Query Language: A language for databases MySQL: A specific database management system (other examples: PostgreSQL, Oracle, Microsoft Access, Filemaker) MySQL Workbench: A GUI for MySQL (other examples: PHPMyAdmin - MySQL for web) 10
Image of page 10
Relational database A collection of related tables , queries, views, triggers/ procedures. Each table/entity describes a real object or concept (e.g. Student or Post) This "concept" is described by different attributes (e.g. name, birthday for the Student table or title, content, date for the Post) 11
Image of page 11
Terminology Name Last_name Gender Zip Danny Olsen M 2200 Emma Erichsen K 2500 Sandra Erichsen K 2500 Entity (also called table or schema) Attributes Values Tuple 12
Image of page 12
When designing the database Which attributes we choose for our table depends on the purpose . Food table - which attributes? Depends … Is the Food table for a: Recipe system? Supermarked? Health platform to compare nutrition, vitamins etc? Children’s game? Same for book, person table etc. 13
Image of page 13
Food table (simplified) Examples Supermarked: food_id, food_name, category, brand, price, weight, size Health platform to compare nutrition, vitamins: food_id, food_name, calories, proteins, fat, carbonhydrates, fiber, calcium, vitamin A, vitamin B1, Vitamin B2 … (long list) R ecipe system: ingredient_id, ingredient_name Children’s game: food_name, color, points 14
Image of page 14
Several tables A database consists of several tables (entities), so for example a recipe database could consist of the tables: recipe ingredient category user (Some errors in datatypes, but just to illustrate … spot the errors later!) Relational model 15
Image of page 15
Image of page 16

You've reached the end of your free preview.

Want to read all 75 pages?

  • Spring '16
  • feofje
  • Relational model

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes