Assignments

Pre-Assignment description

READ ME!

See if you fulfil the condition listed here and take appropriate action.

• If you did not install MySQL on your machine, start at STEP 1, else check next:
• If you have MySQL Workbench or phpMyAdmin installed, go to STEP 3. If not, follow next instruction: - Start at STEP 2.

STEP 1

Install Apache, MySQL and PHP bundle as this will be the quickest and pain-free way to start running your LAB exercises and do the assignments.

# I have installed Apache2, MySQL and PHP@8.0 by homebrew

• Windows & Mac users install MAMP

# I have installed MAMP by homebrew.

• Linux (a bit harder) users install LAMP

Run MAMP and start the server. The lights beside Apache Server and MySQL Server should be green. (NOTE: you do not need apache server unless you want to use phpMyAdmin).

Those of you who installed MAMP will have the phpMyAdmin installed and can be accessed directly in browser by following this procedure:

• Click 'Open WebStart page' on MAMP window; your browser should display MAMP local page.
• Select 'TOOLS' from the menu and click 'PHPMYADMIN'; here you are!

Those of you who feel comfortable with phpMyAdmin can continue using it and skip the next step and go to STEP 3.

STEP 2

Skip this step if you have phpMyAdmin and are comfortable using it.

Install MySQL Workbench!

Run the tool and it should connect automatically. If not, check the port numbers and google for trouble shooting. If nothing works we try to solve it together.

STEP 3

Take a screenshot of your working MySQL Workbench or phpMyAdmin and upload it to the assignment sheet. If it is not working, upload the screenshot of the problem you are receiving.

This text is in Verdana and red

Lecture1: Course Introduction

Lecture1: Course Introduction

Two Courses

• 1DV503 - Database technology
• 1DT903 - Databases and data modeling
• What is different?
  • Assignments and examination will be adjusted for each separately. Major topics discussed are relatively the same.

Some topics covered within this course

• Databases and Database Management Systems (DBMS)
• Database model (ex. Relational model)
• Database design
• Database programming
• Transactions
• ...

Course Structure

• A series of lectures (10 in total)
  • Usually discussing theoretical aspects
• Three Labs/Workshops - practical exercises for DB design, SQL and programing with databases
  • NOTE: Python is the programing language used in this course
• One written assignment
• Two programing assignments
• Written and Oral examinations

People

• Ilir Jusufi
  • Examiner of the course
  • I will post information on consultation hours later once my schedule for this term is fixed.
• 5 TA's

Grading

• You will be graded based on 3 modules:
  • W: Written assignment
  • P: Programming assignments (the average of both submissions)
  • F: Final examination (Written + Oral)
• Final grade: W*0.2 + P*0.4 + F*0.4
• NOTE: You need to pass each of the assignments separately to be able to get a grade!
  • Clarification: You cannot pass the programming module by only passing one of the assignments at maximum points and getting only 50% at the other. You need to have 60% of points on both parts to get a grade!

Grading

Grading - additional information

• WA - Written assignment - individual
• PA Programming assignments:
  • PA1 - individual
  • PA2 - group
• Examination:
  • Written examination (grades E - C)
  • Book a oral exmination for grade (B - A) after passing the written examination
• NOTE: It is possible that you might lower your grade during the oral exam if you come unprepared. For instance: You got C in your written examination. While we test you during oral examination, you fail to answer a basic question, you might end up getting a lower grade than C.

Assignment questions

• Each assignment will have a corresponding forum for questions and communications.
  • SLACK: https://mymoodle.lnu.se/mod/url/view.php?id=3278094

  • ASSIGN FOR TUTORING GROUPS IN MOODLE to be able to submit assignments.

• You are expected to post questions in the appropriate forums/channels before you send us an email. There are around to 300 students, and we often get the same question in tens of times. Therefore, post questions on forum/channels, but first check if it was answered before! This will be the fastest way you will get an answer.

Pre-assignment

• You will get a pre-assignment. This will not be used to grade you but will show your course activity.

  • Therefore, it is mandatory to submit it!

Literature

• Elmasri, Ramez, & Navathe, Shamkant B., Fundamentals of database systems. 7th edition

Lecture2 Database Theory Introduction

Lecture2 Database Theory Introduction

DBMS - Introduction

• Database Management System (DBMS)
  • “Powerful tool for creating and managing large amounts of data efficiently and allowing it to persist over long periods of time, safely” [UW:01]
• DBMS capabilities
  • Persistent storage
    • Large amount of data; exists independent of any processes
  • Programming interface
    • Easy access and modifications through a query language
  • Transaction management
  • Isolation, atomicity, and durability
• Functionalities
  • Allows users to create new databases
  • Specification of a schema (logical structure of data)
  • Data-definition language (DDL)
  • Allows users to query data
  • Query and modify the data
  • Query language and data-manipulation language (DML), e.g. SQL
  • Support the storage of very large amounts of data
    • Secure it from accident or unauthorized use
  • Controls access to data from many users at once
    • Prevent to corrupt the data accidentally
• DBMS vs. File System
• Conventional file systems are inadequate as database systems!
• Why?
• They fail to support:
• Guarantee that data cannot be lost
• Efficient search and complex queries
• Efficient modifications to small pieces of data
• Create a schema (only directory structures)
• Atomic and independent execution of transactions (concurrent access)

Early DBMS

• Old DBMSs: IMS (hierarchy-based) or CODASYL (graph-based)
• Example: Airline Reservation Systems
  • Data
    • Reservations (flight no, seat, meal preference, ...)
    • Flights (departure, destination, departure/arrival times, etc.)
    • Prices
  • Queries
  • Flights on a given date, time, city, seats available, prices
  • Modifications
  • Booking a flight, assigning a seat, ...
  • Requirements
    • Concurrent access by many agents (make sure that no two agents assign the same seat simultaneously)
    • Avoid loss in case of a sudden system failure

Relational Databases

• Based on the relational model
  • [E.F. Codd. A relational model for large shared data banks. Comm. ACM, 13:6, pp. 377-387, 1971]
• Data is organized as tables, called relations
  • Columns: attributes
  • Rows: tuples
  • 
• Separates the logical view from the physical view (storage structure) of the data

Relational Databases

• Querying a Database
  • Find all students who have taken DA2032 in Fall 2007
  • Structured Query Language ─ SQL
    • SELECT student
    • FROM Enrollment
    • WHERE course = ‘DA2032’ AND semester = ‘Fall 2007’
    • Answer: Sjöberg, Arlefur, Roos, ...
  • So-called Query Processor figures out how to answer the query efficiently!

DB in real world

To Be Continued