Lecture 01 Database Design - Part 1.pdf

Database Development Are you ready? 171/181 DBD171/181  Database Concepts Database Design Concepts Entity Relationship Modelling Norma...

Database Development Are you ready? 171/181 DBD171/181  Database Concepts Database Design Concepts Entity Relationship Modelling Normalization Creating Databases and Tables in Access Creating Relationships and Entering Data Module Outline Creating Queries in Design View Writing Queries using Structured Query Language (SQL) Creating Forms in Access Creating Reports in Access Basic Database Networking 2 www.belgiumcampus.ac.za  3 Database development 171/181 IMPORTANT INFORMATION 20% DURATION ASSESSMENTS 10% Class Test 1-3 Per assignment 4 3 assignments/ projects Assessment 4 tests Weights weeks 1 exam 30% Project 40% Summative Test  Recommended Books Harrington, J.L. (2016). Relational Database Desing and Implementation. Morgan Kaufmann. McFadyen, R., 2022. Relational Databases and Microsoft Access 365. Churcher, C., 2012. Beginning Database Design: From Novice to Professional. Apress. 4 www.belgiumcampus.ac.za  LESSON 1 Database Design (Part 1) 5 © BELGIUM CAMPUS 2024 www.belgiumcampus.ac.za  LEARNING OBJECTIVES Basic Database Concepts File Processing System vs Database Systems Database Management Systems Data Models Levels of Data Abstraction 6 6 www.belgiumcampus.ac.za INTRODUCTION Most of our day-to-day activities involve the use of databases. For example: Banking: for all sorts of bank transactions like withdrawal, deposit etc. Airlines: for reserving tickets and to prepare schedules Universities: for student information, registration, grades Sales: customers, products, purchases Online retailers: order tracking, customized recommendations Manufacturing: production, inventory, orders, supply chain Human resources: employee records, salaries, tax deductions Databases touch all aspects of our lives. 7 www.belgiumcampus.ac.za Basic Definitions and Concepts Data: raw facts from which the required information is produced. Information: When data is processed, organized, structured or presented in a meaningful format it becomes information. Data are processed to create information. Database: collection of logically related data stored together to meet information requirements of an organisation. Database Management System (DBMS): program or group of programs to create, process, store, retrieve, control and manage the data stored in the database. The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient. The DBMS software and database together with other components such as the database servers are collectively known as a database system. 8 www.belgiumcampus.ac.za Traditional Filing System The figure below is a conceptual view of a typical manual filing system that consist of people, papers, and filing cabinets. This light-hearted view of a manual database makes the point that paper is the key to a manual database system. Computerised file systems borrow many of the ideas from the manual file filing systems Conventionally, the data were stored and processed using traditional file processing systems. 9 www.belgiumcampus.ac.za FILE PROCESSING SYSTEM A file processing system is a collection of files and programs that access or modify these files. The system stores permanent records in various files. The system needs different application programs to extract records from and add records to the appropriate files. Each program defines and manages its own data. 10 www.belgiumcampus.ac.za Disadvantages of Traditional File System  Data Redundancy : Since each application has its own data file, the same data may be stored in many files.  Data Inconsistency : Data redundancy leads to data inconsistency (incorrect or contradictory information) especially when data is to be updated.  Lack of Data Integration: independent data files exist, and complicated programs have to be developed to retrieve data from every file  Program Dependence: any change in the format or structure of data requires the programs to be modified  Data Dependence: Applications/programs dictate the file organization, physical location and retrieval from the storage media  Limited Data Sharing: Each application has its own private files, and users have limited access data outside their own applications  Poor Data Control: no centralised control at the data  Problem of Security : It is very difficult to enforce security checks and access rights 11 www.belgiumcampus.ac.za Advantages of Database Systems Controlled redundancy Data consistency Program data independence – allows for changes to data without changing the application programs. Sharing of data - Data is centrally controlled and can be shared by all authorized users. Enforcement of standards - constraints or consistency rules can easily be enforced Data integrity means that the data contained in the database is both accurate and Improved data integrity consistent. Improved security - protecting the data contained in the database from unauthorised users. Data access and management is efficient 12 www.belgiumcampus.ac.za What Is a DBMS? A Database Management System (DBMS) is a software package designed to store and manage database It acts as an interface between the application program and the data stored in the database. A DBMS has three main components: Data Definition Language (DDL): allows the users to define the database, specify the data types, data structures and the constraints on the data to be stored in the database. Data Manipulation Language (DML) and Query Language: allows users to insert, update, delete and retrieve data from the database. Two query languages that are widely used in the various commercial RDBMS’s: Structured Query Language (SQL) and Query-By-Example (QBE). Software for Controlled Access of Database - controlled access of the database by the users, concurrency control to allow shared access of the database and a recovery control in case of hardware or software failure. 13 www.belgiumcampus.ac.za THE ROLE OF A DBMS The DBMS Manages the Interaction between the End User and the Database 14 14 www.belgiumcampus.ac.za Why Use a DBMS? Data independence and efficient access. Reduced application development time. Data integrity and security. Uniform data administration. Concurrent access, recovery from crashes 15 www.belgiumcampus.ac.za Why Study Databases? Shift from computation to information Datasets increasing in diversity and volume. Databases driven by some exciting visions e.g. multimedia databases, digital libraries, interactive video, streaming data, Human Genome projects, NASA’s Earth Observation System (EOS) project, etc.... need for DBMS exploding DBMS encompasses most of Computer Science (CS) 16 www.belgiumcampus.ac.za Data Models Models - A data model is a collection of concepts that describe the structure of the database (including data types, relationships and the constraints that apply on the data). A database models real-world enterprise. - Entities (e.g., students, courses) - Relationships (e.g., Madonna is taking course CS564) A schema - is a description of a particular collection of data, using a given data model. The relational model of data is the most widely used model today. - Main concept: relation, basically a table with rows and columns. - Every relation has a schema, which describes the columns, or fields. 17 www.belgiumcampus.ac.za  Example: a database with one relation, the list of employees Relational Databases You should notice this looks just like a two-dimensional table of rows and columns. The name of the table is Employees, each column of the table has its own title, and each row has the same structure. Each row has a value for Employee ID, First Name, Last Name, and Gender. 18 www.belgiumcampus.ac.za General Structure of a Relation The Employees table can be considered a relation of 5 tuples where each tuple has 4 attributes drawn from each of the employee identifier, first name, last name, and gender domains. A domain for an attribute is its set of valid values for an attribute. www.belgiumcampus.ac.za Levels of Abstraction The database system hides certain details of how the data are stored and maintained through several levels of abstraction. Many views, single conceptual (logical) schema and physical schema. Views - describe how users see the data. Conceptual schema - defines logical structure e.g. ERD Physical schema - how the data are actually stored including the files and indexes used. * Schemas are defined using DDL; data is modified/queried using DML. 20 www.belgiumcampus.ac.za Example: University Database Conceptual schema: Students(sid: string, name: string, login: string, age: integer, gpa:real) Courses(cid: string, cname:string, credits:integer) Enrolled(sid:string, cid:string, grade:string) Physical schema: Relations stored as unordered files. Index on first column of Students. External Schema (View): Course_info(cid:string,enrollment:integer) 21 www.belgiumcampus.ac.za Short break… 22 www.belgiumcampus.ac.za