Lecture 1 Introduction to Database Ir. Munawar, MMSI., M.Com.,

Lecture 1 Introduction to Database Ir. Munawar, MMSI., M.Com., PhD Prodi Sistem Informasi dan Teknik Informatika Fakultas Ilmu Komputer

TOPICS BEFORE MID TEST 1. Introduction to Database 2. Relational & Relational Algebra 3. Relational Model 4. Entity Relationship Diagram (ERD) 5. Normalization to 3NF 6. BCNF Normalization to 5NF 7. SQL Data Definition MID TEST

TOPICS AFTER MID TEST 8. More SQL Data Definition 9. SQL Select 10. More SQL Select 11. Yet More SQL Select 12. Missing Information 13. Trigger 14. Review & Case Study FINAL TEST

ASSESSMENT 50 % Group (2 students) Assignment Designing database for real business 20 % Mid Test 30 % Final Test

REFERENCES ‘Database Systems: A practical approach to design, implementation and management’ by Connolly and Begg A first course in database systems’ by Ullman and Widom Other text book : ‘Database Systems’ by CJ Date

Learning Outcomes Student will be able to understand the conceptual design of database Student will be able to describe the important of good database design Student will be able to apply database design for a real business

Why Study Databases? Databases in areCSuseful – Many Databases computing are a ‘core applications topic’ in deal computer with large science amounts of information – Basic Database concepts systems andgive skills a set withofdatabase tools for systems storing, searching are part ofand the skill set managing you will bethis assumed information to have as a CS graduate

What is a Database? “A set of information held in a computer” Oxford English Dictionary “One or more large structured sets of persistent data, usually associated with software to update and query the data” Free On-Line Dictionary of Computing “A collection of data arranged for ease and speed of search and retrieval” Dictionary.com

Databases Train indexes Web timetables Library bookings Airline catalogues Medical Credit card records details Bank accounts Student records Stock control Customer histories Personnel Stock market systems prices Product catalogues Discussion boards Telephone and so on directories

Database Systems Database A databasesystems systemallow consists users of to – Data Store(the database) – Update Software – Hardware Retrieve – Organise Users – Protect We focus mainly on the software their data.

Database Users Database End users Administrator (DBA) – Designs Use the database & manages system the database to achieve system some goal Database Application systems developers programmer – Write Writessoftware the database to allow software end users itselfto interface with the database system

Database Management Systems A Examples: database is a collection of information Oracle management system (DBMS) is the software than A–database – DB2 (IBM) controls that information – MS SQL Server – MS Access – Ingres – PostgreSQL – MySQL

What the DBMS does DBMS Provides provides users with – Data Persistence definition language (DDL) – Concurrency Data manipulation language (DML) – Data Integrity control language (DCL) – Security Often these are all the same language – Data independence Data Dictionary – Describes the database itself

Data Dictionary - Metadata The dictionary holds or catalog stores information about the database itselfof database objects (tables, users, rules, views, indexes, – Descriptions This )is data about data or ‘metadata’ – Information about who is using which data (locks) Almost every aspect of the DBMS uses the dictionary – Schemas and mappings

File Based Systems Problems: File based systems – Data No standards is stored in files – Data Each duplication file has a specific format – Programs Data dependence that use these files depend on knowledge about that format – No way to generate ad hoc queries – No provision for security, recovery, concurrency, etc.

Relational Systems Problems Then, in 1970, with early databases E. F. Codd wrote “A Relational Model of Data for LargetheShared andprograms introduced the – Navigating recordsDatabanks” requires complex relational – There ismodel minimal data independence – No theoretical foundations

Relational Systems Information The relational is model stored as covers tuples 3 areas: or records in relations or tables – Data There isstructure a sound mathematical theory of relations – Data integrity Most modern DBMS are based on the relational model – Data manipulation More details in the next lecture

ANSI/SPARC Architecture ANSI A three-level - American architecture National Standards Institute – Internal level: For systems designers SPARC - Standards Planning and Requirements Committee – Conceptual level: For database designers and administrators 1975 - proposed a framework for DBs – External level: For database users

Internal Level Internal Deals with Schema physical storage of data RECORD EMP – Structure of records on disk - files, pages, blocks LENGTH 44 – Indexes and ordering of records HEADER: BYTE(5) – OFFSET 0 Used by database system programmers NAME: BYTE(25) OFFSET 5 SALARY: FULLWORD OFFSET 30 DEPT: BYTE(10) OFFSET 34

Conceptual Level Conceptual Deals with the Schema organisation of the data as a whole – Abstractions CREATE TABLE are used to remove unnecessary details of the internal level Employee ( – Name Used by DBAs and application programmers VARCHAR(25), Salary REAL, Dept Name VARCHAR(10))

External Level External Provides Schemas a view of the database tailored to a user – Parts of the data may be hidden Payroll: – String Data is presented Name in a useful form – double Used by end Salary users and application programmers Personnel: char *Name char *Department

Mappings Physical Mappings data translate independence information from one level to the next – External/Conceptual Changes to internal level shouldn’t affect conceptual level – Conceptual/Internal Logical data independence These – Conceptual mappings independence levelprovide changes data shouldn’t affect external levels

ANSI/SPARC Architecture User 1 External Schemas User 2 External View 1 User 3 External View 2 External/Conceptual Mappings Conceptual Schema Conceptual View Conceptual/Internal Mapping Internal Schema Stored Data DBA

This Lecture in Exams Describe the three levels of the ANSI/SPARC model. You should include information about what each level is for, which users might be interested in which levels, and how the levels relate to one another.

Next Lecture The Relational Model – Relational data structure – Relational data integrity – Relational data manipulation For more information – Connolly and Begg chapters 3 and 4 – Ullman and Widom (2 ed.) Chapter 3.1, 5.1 – E.F. Codd’s paper