B İL528 – Bilgisayar Programlama II

1. BİL528 – Bilgisayar Programlama II Database Operations

2. 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

3. 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.

4. 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.

5. Exercise • Today, we are going to write a simple database application which displays information about students and courses that students take.

6. 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

7. 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)

8. A Sample Students Table

9. Courses Table • Courses table contains these fields: • Course code • Course name • Instructor • Adding an ID field will make the database operations simpler: • Course ID

10. A Sample Courses Table

11. 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)

12. A Sample Enrolment Table

13. 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.

14. 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

15. Open MS Access

16. Click Office Button and Select New

17. On the right pane, click Browse button

18. Select a folder, a file name, and Office 2002-2003 format with extension *.mdb

19. Click Create Button

20. Right-click Table1 on left-pane and select Design View

21. Give a name to the table and click OK button

22. Write StudentID into the first field

23. Notice the yellow “Primary Key” icon on the left

24. Write “FirstName” into second field and set “Text Size” as 30

25. Write “LastName” into third field and set its “Text Size” as 30

26. Write “BirthDay” to fourth field and set its type as “Date/Time”

27. Write “Age” into fifth field, set its type as “Number” and select “Integer” as its size

28. Close Design View of Students table

29. Click Yes to save the changes

30. Click “Create” tab and select “Table”

31. Right-click Table1 and select Design View

32. Write the name “Courses” and click OK

33. Fill the field names and set their types and sizes. Click Close button.

34. Click Yes to save the changes

35. Give the command Create - Table

36. Right-click Table1 and select Design View

37. Write Enrolment as the table name and click OK

38. “StudentID”, Number, Long Integer

39. “CourseID”, Number, Long Integer

40. Using mouse, select both StudentID and CourseID fields

41. Click “Primary Key” button and notice that both fields become primary keys

42. Close the Design View of Enrolment table

43. Click Yes to save the changes

44. Double-click Students table

45. Fill the records (don’t write anything into StudentID field, it is automatically filled)

46. Double-click Courses table and enter the records

47. Double-click Enrolment table and enter the records

48. Close the MS Access window

49. 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

50. a) Adding Database File into Solution