BİL528 – Bilgisayar Programlama II Database Operations
Contents • Database Structure • Database, Table, Fields, Primary Key, SQL Statements, Select, Insert, Update, Delete • Database Operations with C# Data Objects • Designing database, Adding database into solution, Displaying a table, Displaying a single record • Database Example
Database • A database is an integrated collection of logically related records. • A database contains tables. • The columns of a table is called fields. • The rows of a table is called records. • The records are accessed by SQL commands.
Database Applications • A database application is designed using these steps: • Determine tables and fields • Design database • Write a program which reads and manipulates the database • In this course, we’ll use Microsoft Access to design databases.
Exercise • Today, we are going to write a simple database application which displays information about students and courses that students take.
1. Determining Tables • We need three tables: • Students • Contains information about the students, such as ID number, name, birthday, age, etc. • Courses • Contains information about the courses, such as course code, course name, instructor, etc. • Enrolment • Contains information about which student is enrolled to which course
Students Table • Students table contains these fields: • ID • First name • Last name • Birthday • Age • Notice that these fields are related to a student only (Remember the structs in C)
Courses Table • Courses table contains these fields: • Course code • Course name • Instructor • Adding an ID field will make the database operations simpler: • Course ID
Enrolment Table • Enrolment table contains information about the courses taken by each students. • It has two fields: • Student ID • Course ID • For example, if a record with Student ID = 1 and Course ID = 2, then it means that the student with ID = 1 (Bahar Çağlar) takes the course with ID = 2 (Visual Programming)
Primary Key • Primary key is the field which uniquely identifies each row in a table • A primary key comprises a single column or aset of columns • ID field in Students table and Course ID field in Courses table are primary keys • Both Student ID and Course ID fields in Enrolment table must be primary keys • Always create primary keys for tables.
2. Designing Database • The database design steps are explained in detail in the next slides • The basic steps are • Open Microsoft Access • Create a database file of format Office 2002-2003 with the extension *.mdb • Create tables in design view • Enter records • Close MS Access
Select a folder, a file name, and Office 2002-2003 format with extension *.mdb
Write “Age” into fifth field, set its type as “Number” and select “Integer” as its size
Click “Primary Key” button and notice that both fields become primary keys
Fill the records (don’t write anything into StudentID field, it is automatically filled)
3. Writing the Program • Adding database file into solution • Displaying students • Adding new student • Changing student info • Deleting a student • Displaying all courses a student take