COMP1115 Fundamentals of Database Systems Lecture 1 PDF

COMP1115 – Fundamentals of Database Systems Introduction Course Coordinator Dr. Rashid Ahmad Assistant Professor (Sohar University) Office: Floor 2, Al-Rustaq Building F. Research Interest: Artificial Intelligence Before Sohar University – Brain Pool Research...

COMP1115 – Fundamentals of Database Systems Introduction Course Coordinator Dr. Rashid Ahmad Assistant Professor (Sohar University) Office: Floor 2, Al-Rustaq Building F. Research Interest: Artificial Intelligence Before Sohar University – Brain Pool Research Fellow (2.5 Years) Big Data Research Center ( Jeju, South Korea) – Assistant Professor (6 Year) Comsats University Islamabad, Pakistan The Textbook Fundamental of Database Systems (7th Edition) By Elmasri. Ramez and Navathe. Shamkant You can get the book from the following link with your SU credentials (User Name and Password) https://ebookcentral.proquest.com/lib/soharuni/reader.action?docID=5174454 Lectures and Other Materials All the course materials will be available on SULMS through the following link https://sulms.su.edu.om/ (you have to login with your user name and password) Learning Activities Lectures Labs Lectures The teacher will teach the concepts through PowerPoint slides, white board, etc. Labs You will be performing the practical tasks on your computers while the teacher will be guiding you You will be given some practical tasks to perform on your own. Course Learning Outcomes EXPLAIN THE FUNDAMENTAL CONCEPTS, ARCHITECTURES, AND COMPONENTS OF RELATIONAL DATABASE. IDENTIFY VARIOUS DATABASE COMPONENTS, FUNCTIONAL DEPENDENCIES, ARCHITECTURES, ETC. WRITE RELEVANT SQL STATEMENTS FOR A VARIETY OF DATA DEFINITIONS, DATA CONSTRAINTS, AND DATA MODIFICATION SCENARIOS. CREATE DATABASE USING THE ENTITY-RELATIONSHIP (ER) MODEL. BUILD DATABASE SYSTEM USING APPROPRIATE TOOLS AND TECHNIQUES. The Assessments Assessment Assessment Weighting (%) Due Date Type Description Quiz Formative 0% Week 3 Quiz Summative 10% Week 5 Mid-Term Summative 25% Week 8 Project Summative 15% Week 12 Final Exam Summative 50% Final Exam Week  Database Applications Examples Enterprise Information – Sales: customers, products, purchases – Accounting: payments, receipts, assets – Human Resources: Information about employees, salaries, payroll taxes. Manufacturing: management of production, inventory, orders, supply chain. Banking and finance – customer information, accounts, loans, and banking transactions. – Credit card transactions – Finance: sales and purchases of financial instruments (e.g., stocks and bonds; storing real-time market data Universities: registration, grades Adopted from book slides: Database System Concepts:Seventh Edition:Avi Silberschatz,Henry F. Korth,S. Sudarshan  Database Applications Examples (Cont.) Airlines: reservations, schedules Telecommunication: records of calls, texts, and data usage, generating monthly bills, maintaining balances on prepaid calling cards Web-based services – Online retailers: order tracking, customized recommendations – Online advertisements Document databases Navigation systems: For maintaining the locations of varies places of interest along with the exact routes of roads, train systems, buses, etc. Adopted from book slides: Database System Concepts:Seventh Edition:Avi Silberschatz,Henry F. Korth,S. Sudarshan  Purpose of Database Systems In the early days, database applications were built directly on top of file systems, which leads to: Data redundancy and inconsistency: data is stored in multiple file formats resulting induplication of information in different files Difficulty in accessing data – Need to write a new program to carry out each new task Data isolation – Multiple files and formats Integrity problems – Integrity constraints (e.g., account balance > 0) become “buried” in program code rather than being stated explicitly – Hard to add new constraints or change existing ones Adopted from book slides: Database System Concepts:Seventh Edition:Avi Silberschatz,Henry F. Korth,S. Sudarshan  Purpose of Database Systems (Cont.) Atomicity of updates – Failures may leave database in an inconsistent state with partial updates carried out – Example: Transfer of funds from one account to another should either complete or not happen at all Concurrent access by multiple users – Concurrent access needed for performance – Uncontrolled concurrent accesses can lead to inconsistencies Ex: Two people reading a balance (say 100) and updating it by withdrawing money (say 50 each) at the same time Security problems – Hard to provide user access to some, but not all, data Database systems offer solutions to all the above problems Adopted from book slides: Database System Concepts:Seventh Edition:Avi Silberschatz,Henry F. Korth,S. Sudarshan  University Database Example In this text we will be using a university database to illustrate all the concepts Data consists of information about: – Students – Instructors – Classes Application program examples: – Add new students, instructors, and courses – Register students for courses, and generate class rosters – Assign grades to students, compute grade point averages (GPA) and generate transcripts Adopted from book slides: Database System Concepts:Seventh Edition:Avi Silberschatz,Henry F. Korth,S. Sudarshan  Basic Definitions Data Data refers to raw facts, figures, and symbols that represent events, measurements, or observations. It is unprocessed and lacks context, meaning, or interpretation. Examples: Numbers, text, images, sensor readings. Adopted from: https://www.diffen.com/difference/Data_vs_Information  Information Information is data that has been processed, organized, or structured to convey meaning, relevance, or understanding. It provides context, significance, and insight. Examples: Reports, summaries, analysis, conclusions. Adopted from: https://www.diffen.com/difference/Data_vs_Information  Characteristics of Data Objective: Data is objective and neutral, devoid of bias or interpretation. Raw: It is unprocessed and lacks organization or structure. Abundant: Data can be abundant and overwhelming in quantity. Incomplete: Data may not provide a complete picture without context. Adopted from: https://www.diffen.com/difference/Data_vs_Information  Characteristics of Information Subjective: Information reflects human interpretation and judgment. Processed: It results from the organization, analysis, or transformation of data. Relevant: Information is pertinent to a particular context or purpose. Complete: It presents a comprehensive view or understanding. Adopted from: https://www.diffen.com/difference/Data_vs_Information Data vs. Information Examples Data Information each individual homework and test the student’s average grade for grade of a student in one class each class typing the words “Universities in the list of search results that Oman” in your computer search includes the list of universities in engine (input) Oman (output) A series of temperature readings An average temperature of (e.g., 24.5°C, 23.8°C, 25.0°C). 24.4°C, indicating a mild climate. 96374263 a person’s phone number the freezing and boiling points of 100, 212, 0, 32 water in Fahrenheit and Celsius Data → Information → Knowledge Credit: https://internetofwater.org/ Database A collection of related data. Adopted from: Elmasri. Ramez and Navathe. Shamkant. B. (2017), fundamental of Database Systems, 7th Edition Database Management System (DBMS) A software used for the creation and maintenance of a computerized database is called a database management system (DBMS) Examples are: Microsoft Access, MySQL, Oracle, SQL Server  DBMS vs File System Credit: https://www.shiksha.com/ Record A record is a collection of related data elements treated as a single unit. It represents a single instance or entity within a database. In a customer database, a record might contain information about one specific customer. Field A field is a single piece of data within a record. Fields represent specific attributes or characteristics of the entity described by the record. For example, in a student record, fields might include "student ID," "name," and "birthdate." Table A table is a fundamental database component used to store data. It consists of rows and columns. Each row represents a record, and each column represents a field. Tables provide structure to the data.  Example Employee ID First Name Last Name Date of Birth Department Salary 001 Muhammad Ali 1985-03-15 HR OMR600 002 Fatema AlJabri 1990-08-22 IT OMR700 003 Ayesha AlMaqbali 1988-12-05 Sales OMR550 Records (Rows): Record 1: Employee ID 001, Muhammad Ali, 1985-03-15, HR, OMR600 Record 2: Employee ID 002, Fatema AlJabri, 1990-08-22, IT, OMR700 Record 3: Employee ID 003, Ayesha AlMaqbali, 1988-12-05, Sales, OMR550 Fields (Columns): Employee ID First Name Last Name Date of Birth Department Salary  History of Database Systems 1950s and early 1960s: – Data processing using magnetic tapes for storage Tapes provided only sequential access – Punched cards for input Late 1960s and 1970s: – Hard disks allowed direct access to data – Network and hierarchical data models in widespread use – Ted Codd defines the relational data model Would win the ACM Turing Award for this work IBM Research begins System R prototype UC Berkeley (Michael Stonebraker) begins Ingres prototype Oracle releases first commercial relational database – High-performance (for the era) transaction processing Adopted from book slides: Database System Concepts:Seventh Edition:Avi Silberschatz,Henry F. Korth,S. Sudarshan  History of Database Systems (Cont.) 1980s: – Research relational prototypes evolve into commercial systems SQL becomes industrial standard – Parallel and distributed database systems Wisconsin, IBM, Teradata – Object-oriented database systems 1990s: – Large decision support and data-mining applications – Large multi-terabyte data warehouses – Emergence of Web commerce Adopted from book slides: Database System Concepts:Seventh Edition:Avi Silberschatz,Henry F. Korth,S. Sudarshan  History of Database Systems (Cont.) 2000s – Big data storage systems Google BigTable, Yahoo PNuts, Amazon, “NoSQL” systems. – Big data analysis: beyond SQL Map reduce and friends 2010s – SQL reloaded SQL front end to Map Reduce systems Massively parallel database systems Multi-core main-memory databases Adopted from book slides: Database System Concepts:Seventh Edition:Avi Silberschatz,Henry F. Korth,S. Sudarshan  Practice Q1. Define the following – Data – Record – Information – Database – Database Management System (DBMS) Q2. Classify each of the following into data or information. – Temperature readings (e.g., 75°F, 20°C). – A weather forecast stating, "Today will be sunny with a high of 75°F (24°C)." – A student's report card showing grades for different subjects, indicating whether the student passed or failed. – A list of student exam scores (e.g., 85, 92, 78). – A medical diagnosis indicating that the patient has hypertension and is at risk of heart disease. – Daily blood pressure and heart rate measurements. Q3. Provide some examples of databases that you have used/accessed.  Practice Q1. Provide examples for the following – Data – Information – Database – Field – Database Management System (DBMS) Q2. Classify each of the following into data or information. – Students' details, such as birthdates, addresses, and phone numbers. – Majority of students belong to Sohar – 0, 100 – 0°C and 100°C are the freezing and boiling points of water – Student names and their test scores (e.g., Ali: 92, Akram: 78, Ahmad: 85). – A report saying, "Ali scored the highest in the class." Q3. Provide an example of database and its data for your school.