9.1 Basic Concepts of Database

1. The Structure of a Database A database is a structured collection of data and consists of one or more tables. A table in turn contains records and fields. A record is a row which contains a set of properties related to a particular item while a field is a column which contains a specific property for all items. A well-designed database enables huge data storage and efficient data retrieval. 9.1Basic Concepts of Database

2. 9.1Basic Concepts of Database • The Structure of a Database Structure of a database

3. 9.1Basic Concepts of Database • Primary Key • A primary key is a field or a set of fields in a table. • Each record’s primary key is a unique value for identifying itself among all the records in the same table. • Primary keys can be used to establish relationships among tables. • In the following table, the field ‘Student ID’ can be used as a primary key since all the students are assigned a unique student identity number.

4. 9.1Basic Concepts of Database Primary key • Primary Key A table of students’ information

5. 9.1Basic Concepts of Database Primary key • Primary Key A table of students’ information

6. 9.2Using Database Software • When we manipulate a large amount of data, manual processing is proved to be both tedious and inefficient. • Therefore, we need to use database software to process database records. • With database software, relevant information can be accessed and updated efficiently with built-in functions • sorting • searching • report generation

7. 9.2Using Database Software • Common database software • Microsoft Access • FoxPro • Oracle • MySQL • They have different processing capacities that fit into different scales of applications.

8. 9.2Using Database Software • Working Environment of Microsoft Access • In Microsoft Access, the database window enables users to create various objects in the database. • After an object is created, it can be accessed in this window. Title bar Toolbar Creation mode Objects column Existing objects The working environment of Microsoft Access

9. 9.2Using Database Software • Tables • A database can consist of multiple tables which are structured collections of records. • Each record is one single row in a table and each record consists of many fields. An example of a table

10. 9.2Using Database Software • Query • A set of user-defined criteria for retrieving the relevant information from the database • It must be written in a specific format. Microsoft Access provides different tools to help users compose a query. • After a query is submitted, the requested data will be retrieved and the result will be displayed to users. A query in design (left) and its executed result (right)

11. 9.2Using Database Software • Form • An interface created by users to manipulate and display data • It can also serve other purposes such as user menus and message display. An example of a form

12. 9.2Using Database Software • Report • Present information in a user-defined format. • It can be viewed on screen or printed on paper. An example of a report in the Print Preview

13. 9.2Using Database Software • Views of Objects • In Microsoft Access, each object can be displayed in different ways with different views. List of different views for different objects

14. 9.2Using Database Software • Views of Objects The Datasheet View A table in the Design View

15. 9.2Using Database Software • Views of Objects The Datasheet View A table in the Design View

16. 9.3Creating a Table in Microsoft Access • Methods of Creating a Table • In Microsoft Access, there are three ways to create a table in a database. • By using the Design View; • By using the Table Wizard; • By entering data into Datasheet. • Using the Design View is the most preferred method to create a table since it allows users to define precisely the properties of each field such as name, data type and size.

17. 9.3Creating a Table in Microsoft Access • Methods of Creating a Table • The other two methods only allow users to create tables of rough designs and sooner or later, the table structures will probably need to be refined in the Design View anyway. • It is always a good practice to design thoroughly first before implementation and therefore the latter two methods are not recommended to build a robust database.

18. 9.3Creating a Table in Microsoft Access • Creating a Table in Design View • The Design View window is divided into two parts • Top pane • Enter the field names, data types and an optional description of the fields. • Bottom pane • Specify field properties. Top pane Bottom pane Working environment in Design View

19. 9.3Creating a Table in Microsoft Access • Creating a Table in Design View • The Design View window is divided into two parts • Top pane • Enter the field names, data types and an optional description of the fields. • Bottom pane • Specify field properties. Top pane Bottom pane Working environment in Design View