Transcript Databases - Tempus Project Site

Project IB_JEP-41148-2006 (RS)
University of Niš
Faculty of Electronic Engineering
Department of Computer Science
COURSE: DATABASES
Prof.dr Leonid Stoimenov, mr Aleksandar Stanimirović,
dipl.ing Miloš Bogdanović
General information


Course: Databases
Lecturers and authors of materials
 Prof.
dr Leonid Stoimenov
 Mr. Aleksandar Stanimirović
 Dipl. ing. Miloš Bogdanović


Scheduled: June 19.,20.,21 2009.
Lessons:
3
sections
 8 lessons
Goals





Basics of relational databases
How to create relational database
How to query data
How to manipulate data
Database applications
 MS
Access
 ADO.NET
 Desktop and Web applications
Literature



D.Kroenke, D.Auer, Database Concepts, Third Edition,
Pearson Prentice Hall, 2008.
S.Đorđević-Kajan, L.Stoimenov, Baze podataka,
praktikum za vežbe na računaru, Elektronski fakultet u
Nišu, Edicija pomoćni udžbenici, 2004.
R. Elmastri & S. Navathe, Fundamentals of Database
Systems, Pearson International Education, Addison
Wesley, 5th edition, 2007.
Lessons overview

Section I : Introduction:


Lesson 1: Databases and relational model
Section II : SQL
Lesson 2: Data definition (CREATE TABLE)
 Lessons 3 and 4: Queries (SELECT)
 Lesson 5: Data manipulation (INSERT, UPDATE, DELETE)


Section III: Application development
Lesson 6: MS Access – applications, forms, reports
 Lesson 7: ADO.NET
 Lesson 8: Desktop and Web database applications

Section I : Databases and relational
model
COURSE: Databases
Lesson 1: Databases and relational model

Introduction
Database
 Database Management System (DBMS)


Data models


Categories of Data models
Relational data model
Characteristics of Relations
 Examples
 Key constraints
 Referential integrity and foreign keys


Exercises
Lesson 1: Databases and relational model

Relational databases
What are relational databases?
 Relations represented as two-dimensional tables
 Table columns
 Table rows
 Constraints


Database Management System (DBMS)
What is Database Management System?
 Basic components



Database applications
Exercises
Lesson 1: Databases and relational model

Personal DBMS
Lesson 1: Databases and relational model

Company DBMS
Lesson 1: Databases and Relational model

Example: Database PREDUZEĆE (COMPANY)
RADNIK
LIME
SSLOVO
PREZIME
MATBR
...
DATRODJ
PLATA
POL
ADRESA
PROJEKAT
NAZIV
LOKPR
BROJPR
BRS
MATBRR
DATPOST
SEKTOR
NAZIV
SBROJ
CLAN_PORODICE
MATBRRAD
IME
POL
SRODSTVO
LOK_SEKTOR
BRS
LOKACIJA
DATRODJ
RADI_NA
MBR
BRPR
SATI
...
MATBROJS
BRSEK
Lesson 1: Databases and Relational model

Exercises
Zadatak 4: Relacije
Na osnovu zahteva koji su dati prepoznati relacije i njihove atribute, definisati
odgovarajuće tabele i njihove primarne ključeve:
(a) Brod: brod ima ime, registracioni kod, bruto nosivost, i godina igradnje.
Restoran: restorani imaju naziv, adresu, broj mesta, telefon, i vrstu hrane (roštilj, riba,
pice).
Zadatak 7: Baza podataka PREDUZEĆE
Na osnovu navedenih zahteva projektovati relacionu šemu baze podataka
PREDUZEĆE.
Zahtevi:
Preduzeće ima više sektora. Svaki sektor ima ime, broj i rukovodioca. Sektor ima bar
Section II: SQL
COURSE: Databases
Lesson 2: SQL and Data Definition


Introduction
Database PREDUZEĆE (COMPANY)
 Tables
(definitions and structure)
 Test data

SQL and Data Definition (CREATE TABLE)
 Data
types
 Column constraints
 Table constraints

Exercises: MS Access
Lesson 2: SQL and Data Definition

Example: Database PREDUZEĆE (Company)
Lessons 3 i 4: Queries (SELECT)






SELECT keyword
SELECT and FROM
clauses
WHERE clause
ORDER BY clause
Arithmetic and
aggregate functions
Exercises: MS Access





GROUP BY and
HAVING clauses
Table joins
Cartesian product
(cross-join)
Different types of table
joins
Combining results from
multiple queries
Lessons 3 and 4: Queries (SELECT)
SELECT MAX(Plata) AS MaxPlata, MIN(Plata) AS MinPlata,
AVG(Plata) AS ProsPlata, SUM(Plata) AS UkPlata
FROM RADNIK;
Primer 16
MaxPlata MinPlata ProsPlata UkPlata
55000
25000
34875
279000
SELECT R.MatBr, R.ime, R.Prezime, P.Naziv
FROM RADNIK R, RADI_NA RN, PROJEKAT P
WHERE R.MatBr = RN.Radnik AND RN.Projekat = P.Broj AND R.pol = 'Ž';
MatBr
ime
Primer 11
Prezime
Naziv
999887777 Valentina
Kovačević Godišnji izveštaj
999887777 Valentina
Kovačević Reorganizacija
987654321 Aleksandra Petrović
Godišnji izveštaj
987654321 Aleksandra Petrović
Informacioni sistem
453453453 Jelena
Janković
ProizvodX
453453453 Jelena
Janković
ProizvodY
Lesson 5: Data manipulation




Adding new rows
Modifying existing rows
Deleting existing rows
Modifying relational database scheme
 Deleting
tables
 Modifying tables
Primer 6
U ovom primeru se koristi UPDATE...SET naredba da bi se lokacija projekta čiji je
broj 5 promenila na vrednost 'Beograd'.
UPDATE PROJEKAT
SET Lokacija = 'Beograd'
WHERE Broj = 5;
PROJEKAT
Broj
Naziv
Lokacija Sektor
1 ProizvodX
Niš
5
2 ProizvodY
Pirot
5
Section III: Application development
COURSE: Databases
Lekcija 6: MS Access – applications, forms, reports





Introduction
Forms
Reports
Macros
Access application
Lesson 7: ADO.NET







ADO.NET basics
ADO.NET data providers
ADO.NET direct data access
Connection object
Command object
DataReader object
DataSet object and connectionless data access
SqlConnection conn = new SqlConnection();
String strSQL = “Select * from RADNIK”;
SqlCommand comm = new SqlCommand(strSQL, conn);
SqlCommand newComm = new SqlCommand();
newComm.Connection = conn;
newComm.CommandText = strSQL;
Lekcija 8: Desktop and Web database
applications



Introduction
Windows Forms controls data bindings
ASP.NET controls data bindings
DataGridView dgv = new DataGridView();
SqlConnection conn = new SqlConnection();
conn.ConnectionString
=
"Data
Source=localhost;User
Id=admin;Password=admin;Initial Catalog=PREDUZECE;";
String strSQL = "Select * from RADNIK";
SqlCommand comm = new SqlCommand(strSQL, conn);
SqlDataAdapter adapter = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
try
{
conn.Open();
adapter.Fill(ds, "Radnik");
conn.Close();
dgv.DataSource = ds.Tables["Radnik"];
dgv.Width = this.Width;
this.Controls.Add(dgv);
}
catch(Exception exc)
{
//obrada izuzetka
}
Course implementation
COURSE: Databases
Course implementation

Lessons schedule
 Databases
and relational model – May 25
 Data definition (CREATE TABLE) – May 29
 Queries (SELECT) – June 2
 Data manipulation (INSERT, UPDATE, DELETE) – June 5
 MS Access – applications, forms, reports – June 9
 ADO.NET – June 12
 Desktop and Web database applications – June 15
Course implementation

Lessons were created using exeLearning
Course implementation

Moodle course with imported SCORM packages
Course implementation

Lesson 1 (SCORM package)
Course implementation

Moodle calendar
Course implementation

Moodle forum
Course implementation

Moodle assigment
Course implementation

Moodle quiz
DATABASES
Contact info