Database Intermediate First Session- 23 July.pptx

This describes Databases and what is databases is made up such as tables and rows.

1. Database (Intermediate) 23/07/2024 Ms M.Khomo

2. Learning Objectives. Explain the main components of a database system. Discuss the table as the most basic database object. Explain the concept of primary keys and foreign keys. Compare the four system databases. Describe the main functions of a database management system(DBMS). Discuss the types of DBMS. Compare DBMS and File System Data Management. Plan a well -designed database.

3. 01 Why do we have Databases? What makes up a Database? What is a Database? 02 03

4. What is a Database? A database is an organized collection of data that is stored and accessed electronically. It allows for efficient storage, retrieval, and management of data. Databases are used to store information in a structured way, making it easy to search, update, and analyze the data.

5. Why do we have databases? Organize: data in a structured way. Retrieve: data quickly and easily. Update: and manage data consistently. Ensure: data accuracy and integrity. Support: multiple users and applications accessing the data simultaneously. This makes databases essential for handling large amounts of information in various applications, such as business, education, and technology.

6. Tables: The primary structures in a database, where data is stored in rows and columns. Columns (Fields ): Define the type of data stored, such as integer, text, date, etc. Rows (Records): Individual entries in a table, each containing a set of values for each column. Primary Key : A unique identifier for each record in a table. Foreign Key: A field in one table that links to the primary key of another table, establishing relationships between tables. Main Components of Database System.

7. Indexes: Structures that improve the speed of data retrieval operations on a table. Views: Virtual tables that provide a way to look at data from one or more tables. Stored Procedures: Precompiled SQL statements that perform specific tasks and can be reused. Triggers: SQL code that automatically executes in response to certain events on a table. Schemas: Logical groupings of tables, views, and other database objects, often used to organize and secure data. Main Components of Database System cont...

8. Example of a Table Columns Rows

9. As discussed, a database is made of multiple things, but the main thing are TABLES. Tables consists of domain data (columns ) and entity data(rows). The actual data for the database is stored in the tables. Most Basic Data Object: Table

10. *The number of Columns will remain the same regardless of how much data is entered in the table. However, the number of records will go up and down as you add or delete data. The nature of the data in each record is described and restricted by the data type ( int, varchar Most Basic Data Object: Table

11. What is a primary key? Primary Key : A primary key is a unique identifier for each record in a database table. It ensures that each record is distinct and can be referenced precisely. No two rows in a table can have the same primary key value. Foreign Key : A foreign key is a field in one table that links to the primary key in another table. It creates a relationship between the two tables, allowing them to be connected and ensuring data consistency. Primary Key vs Foreign Key

12. `Students` and `Registration`. - Students Table: - ` StudentID ` (Primary Key) - `Name` - `Major` - Registration Table : - ` RegistrationID ` (Primary Key) - ` StudentID ` (Foreign Key) - ` CourseID ` Primary Key vs Foreign Key: Example In this example, ` StudentID ` in the `Registration` table is a foreign key that links to the ` StudentID ` primary key in the `Students` table. This relationship allows us to associate each enrollment record with a specific student.

13. Think about the Shein Database: What tables would they have? What columns would each table have ? Are there Primary Keys / Foreign Keys in these table? If so, what are they? Let’s Practice

14. Products Table : ProductID (Primary Key) ProductNameCategory PriceStockQuantity SupplierID (Foreign Key) Possible Answers: Customers Table : CustomerID (Primary Key) Name Email PhoneNumber Address

15. Possible Answers Cont : Orders Table: OrderID (Primary Key) CustomerID (Foreign Key) OrderDate TotalAmount OrderDetails Table: OrderDetailID (Primary Key) OrderID (Foreign Key) ProductID (Foreign Key) Quantity PricePerUnit

16. Possible Answers Cont : Suppliers Table : SupplierID (Primary Key) SupplierName ContactInfo

17. Possible Answers Cont : Products and Suppliers are linked via SupplierID . Orders are linked to Customers via CustomerID . OrderDetails link Orders and Products via OrderID and ProductID .

18. Remember: We associate different tables in a database to: 1. Organize Data: Keep related data in separate tables for clarity and manageability. 2. Reduce Redundancy : Avoid storing the same data multiple times, saving space and reducing errors. 3. Maintain Integrity : Ensure data consistency and accuracy by linking related information. 4. Facilitate Queries :Simplify complex data retrieval by joining related tables. This helps in efficient data management and ensures that the database remains scalable and easy to update. Separate departments need different tables.

19. Thank You See You Later!