COURSE OUTLINE AND GROUND RULES

 

Course ID         CE223

 

Course Name       Database Systems

 

Semester          Spring, 2014

 

Instructor        Prof. Dr. Mehmet E. DALKILIÇ

 

E-Mail            mehmet.emin.dalkilic@ege.edu.tr

                             

Lecture Hours     Wednesday        11:00–13:50 C402 Section 2 (Software Engineering)

                                  

Lab Hours         Wednesday        16:00-17:50 C605 Section 2 (Software Engineering)

                                  

Office Hours      Wednesday       15:00 – 16:00

 

Final exam will be given on June 2nd 2014

15:00 – 18:00 hours at C401 and C402

Sample final exam from year 2006

 

Student ID

FINAL EXAM

20090601022

23

20090601052

61

20090601057

37

20090602006

27

20100601011

7

20100601012

43

20100601014

33

20100601017

28

20100601021

39

20100601026

39

20100601028

55

20100601037

15

20100602010

40

20100602064

10

20110601002

54

20110601005

39

20110601007

49

20110601008

76

20110601009

59

20110601012

20

20110601014

68

20110601017

31

20110601020

9

20110601021

44

20110601025

40

20110601026

44

20110601028

89

20110601030

15

20110601032

23

20110601035

68

20110602007

79

20110602015

29

20110602030

37

20110602032

41

20110602034

46

20110602035

37

20120601001

84

20120601004

42

20120601014

63

20120601029

26

20120601036

20

20120601037

62

20120601054

57

 

 

QUIZ  #5 will be given in the first lab hour on May 28th, 2014. (Topics: CLI/JDBC/PHP,

Transactions/Views/Indexes)

 

Don’t be late!!

 

Lab. #6 (due date: May 21th, 2014)

1. Answer the given questions by writing appropriate triggers. Note that you do not have to follow MySQL syntax.

2. Print out a copy of the preliminary lab sheet, solve the questions provided and write your solutions on the printed copy.

Lab 6 preliminary worksheet

 

3. Hand in your solutions at the beginning of the lab.

 

 

Lab. #5 (due date: May 14th, 2014)

 

1.      Reimport Ships database to restore lost data from Lab. #4.

 

2.      Print out a copy of the preliminary lab sheet, solve the questions provided and write your solutions on the printed copy.

 

Lab 5 preliminary worksheet

 

3.      Hand in your solutions at the beginning of the lab.

 

 

QUIZ  #4 will be given in the first lab hour on May 14th, 2014. (Topics: SQL2, Constraints/Triggers and PSM/Embedded SQL)

Don’t be late!!

 

 

CE 223 Database Systems (Spring 2014)

Lab. #4 (due May 7th, 2014)

 

Import the shýps database provided on the website of the course and write the following queries in SQL.

Fill in the tables after each question. If a query results in more than 10 rows (tuples) give only the

first five and last five rows and the number of rows returned. Note that you can check the exercises

2.3.2 (page 37) and 2.4.3 (page 55) from course book to understand the schema better.

Write and run MySQL queries for the following questions.

Also provide the query results. If a query results in more than 10 tuples

give only the first five and last five rows (tuples)

 

1.     Find the ships heavier than 35000 tons (displacement).

2.     Find for each class the year in which the first ship of that class was launched.

3.     Find the names of all ships whose name consists of three or more words.

4.     Find those countries that have both battleships and battlecruisers.

5.     Find for each class the number of ships of that class sunk in battle.

6.     List all the ships mentioned in the database. (Remember that all these ships may not appear in the Ships relation.)

7.     Find the average number of guns of battleship classes.

8.     Find the average number of guns of battleships (Note the difference between this one and the previous one;

do we weight a class by the number of ships of that class or not?)

9.     The two British battleships of the Nelson class – Nelson and Rodney – were both launched in 1927, had 16-inch

guns and a displacement of 34000 tons. Insert these facts into the database.

10.  Delete from Ships relation all ships sunk in battle.

Here is a link to a suitably formatted file for  above Lab-preliminary work.

 

 

 

CE 223 Database Systems (Spring 2014)

Lab. #3 (due April 30th, 2014)

 

Write and run MySQL queries for the following questions.

Also provide the query results. If a query results in more than 10 tuples

give only the first five and last five rows (tuples)

 

 

  1. Find those country names that start with the string Turk.

 

  1. Find the largest city (and its population) in the world

 

  1. Find the smallest city (and its population) in the world

 

  1. Find the total population over all cities

 

  1. Find the total population over all countries

 

  1. Find the language spoken by largest population.

 

  1. List countries by population (descending order)

 

  1. Find the names of countries having at least one city with a population greater than 5 million.

 

  1. Find among the countries of Query #7 where the official language is English.

 

  1. Find Those Langugaes spoken in at least two countries.

 

 

 

Midterm Exam on April 21st, 2014 at 18:30 – 20:30 (C103 and C104)

 

 

Lab Assignment #2 (due April 9, 2014)

 

Bring a written copy of your work to lab. Your work should contain the SQL statement as well as the output.

 

Write and run MySQL queries for the following questions. Also provide the query results.

If a query results in more than 10 rows (tuples) give only the first five and last five rows.

In each case, indicate the number of rows the query returns.

 

1. Find those Africa cities with a population of more than 2 Million.

 

2. Find the number of distinct langues in CountryLanguage table.

 

3. Find those cities that are unique (the only city) in their districts.

 

4. Find the languages spoken in Europe.

 

5. Find those Middle East countries with a LifeExpectancy longer than the average LifeExpectancy of the European Countries.

 

6. Find those countries where official language is english and population is greater than 10 Million.

 

 

QUIZ  #3 will be given in the first lab hour on April 9th, 2014. (Relational Algebra and SQL 1)

Don’t be late!!

 

Link to World Database File

Lab Assignment #1 (due April 2nd, 2014)

  1. Download and install MySQL5.1 database server and client for windows (mysql-5.1.19-win32)

http://dev.mysql.com/downloads/mysql/

  1. Download and install MySQL Query Browser (mysql-query-browser-1.1.20-win.msi)

http://dev.mysql.com/downloads/workbench/

  1. Download and setup and start  experimenting (playing with) the example database “world database”

which contains “city”, “country” and “countrylanguage” tables.

http://dev.mysql.com/doc/world-setup/en/world-setup.html 

Note: Try to find some interesting facts (e.g., list of countries having at least one city with a population more than 5 million)

Use the help facilities of http://dev.mysql.com/ for further assistance.

Important note about Lab Assignment #1: Every student should download and install MySQL on his/her computer/Laptop.

MySQL will be installed to Lab computers (C602, C605, C608) by course TA’s. Students who wants to use their Laptops

in the Lab (instead of Lab PCs) must give their names to the instructor on the firs Lab day that is April 3rd, 2013.

Depending on the availability of the electrical outlets in the Lab, the Instructor will determine (by random selection if demand is

greater than the supply) the students who will use their Laptops during Lab sessions. However, please note that once you request and

being granted to use your Laptop in Lab sessions, there is no returning back to using Lab PCs; it is a one time choice – once the lists are

determined (on who use Laptops and who use Lab PCs) your choice is  fixed for the semester.

 

                 

Quiz # 2 will be given at 16:00 – 16:20 (in the first Lab. Hour) on March 26th, 2014.

Don’t be late!!! Quiz covers Week3 and 4 material i.e., FD’s (BCNF, 3NF) and MVD’s (4NF)

 

Quiz # 1 will be given at 16:00 – 16:20 (in the first Lab. Hour) on March 12th, 2014.

Don’t be late!!!

 

Objectives

The goal of this course is to give basic knowledge of database systems to a student who intends to be a computer

or software engineer. It provides a comprehensive introduction to relational data model and entity relationship

data model as a design tool. Functional and multivalued dependencies in the context of normalization process

are described in detail for designing relational database schema. SQL database language and system aspects of SQL

such as transaction management, indexing, constraints, triggers and authorization are studied in detail together

with laboratory practices illustrating different ways of database programming.

 

Course Outline

 

Week

Chap. Sect.

Link

Topic

01(Feb26)

1, 2, 11

INTRODUCTION

Introduction, Relational Data Model, XML

02(Mar05)

4

ER MODEL

Entity-Relationship Data Model

03(Mar12)

3

DESIGN 1 FD's

Design of Relational Databases, Functional Dependencies

04(Mar19)

3

DESIGN 2

Design of Relational Databases, Multivalued Dependencies

05(Mar26)

2, 5

RA1  RA2

Relational Algebra

06(Apr02)

6

SQL1

Introduction to SQL (Part I)

07(Apr09)

6

SQL2

Introduction to SQL (Part II)

08(Apr16)

 

 

Midterm/Review

09(Apr30)

7

CONS

Constraints and Triggers

10(May07)

9

REAL

Real SQL Programming

11(May14)

9

CLI-JDBC-PHP

CLI, JDBC, PHP

12(May21)

6, 8

TRANS-VIEW-INDEX

Transactions, Views, Indexes

13(May28)

10

AUTHORIZATION

SQL Authorization

 

Textbook

 

J. D. Ullman and J. Widom, A First Course In Database Systems, 3rd Ed., Prentice-Hall,

 ( ISBN-10: 013600637X, ISBN-13: 9780136006374).

 

Reference Book

 

Silberschatz et. al., Database System Concepts, 4th ed., McGraw-Hill, 2002

 

Web References

 

Syllabus           http://ects.ieu.edu.tr/syllabus.php?section=ce.cs.ieu.edu.tr&course_code=CE%20223

 

Textbook home page  http://www-db.stanford.edu/~ullman/fcdb.html

 

Course home page       http://ube.ege.edu.tr/~dalkilic/DataBaseSpr2014.htm

Assignments

 

To fully prepare for the exams, study lecture notes, go through the examples in the text book and study/solve

the exercises in the text book to test your understanding of the subject. In addition, make sure that you go through

the starred exercises (with solutions in the text book’s website) and understand them.

 

Quizes

 

 

Late Comers

 

 

Exams

 

 

Attendance

 

for the first one or two weeks he/she has missed

is your overall attendance percentage (including lectures and labs) throughout the semester

 

Participation

 

that will form the basis of attendance grade

points (not greater than 5) accumulated throughout the semester.

Note that if you do not earn any participation point, then you get zero as your participation grade. 

Requests

·         The sections are completely different classes. There will be no transfers or make-ups between sections for any reason whatsoever

 

 

Evaluation:

 

Midterm  Exam          %25

Quizes                        %15

Lab                             %15

Final Exam                 %35

Attendance                 %05

Participation              %05

 

Announcements

The students are expected to check their e-mails and the web site of the course for the announcements.