info 2102 l1 database review
TRANSCRIPT
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
INFO 2102 DATABASE SYSTEMS II
Dr. Lili Marziana Abdullah
International Islamic University MalaysiaDepartment of Information Systems
Kulliyyah of Information & Communication Technology
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
INFO 2102 – Database Systems II
Lecturer: Dr. Lili Marziana Abdullah Room: C5-42 or CITA office, level 3 Email: [email protected] Consultation Times:
Monday and Wednesday, 11.30 am – 12.50 pm
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Reminder… Regularly access INFO 2102 Course Repository. Textbook:
Required:Rosenzweig, B and E.S Rakhimov (2008), Oracle PL/SQL by Example, Fourth Edition, Prentice Hall.Patrick, J. (2009) SQL Fundamentals, Third Edition, Prentice Hall.
Online:http://www.oracle.com/technetwork/index.html
Submit copy of MC/valid excuse letter IMMEDIATELY when you return to class. Pass to classmate for prolonged absence. Delayed submission will not be accepted.
Submit any assignment or exercise ON TIME. Delayed submission will not be accepted.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Reminder… DO come ON TIME to the class. I can only
tolerate 15 mins late. You will NOT BE ALLOWED to enter class if later than 15 mins.
DO observe the university rule on DRESS CODE and CONDUCT as IIUM STUDENTS.
DO NOT go in and out from class. DO NOT open any social networking websites,
chatting applications or any unrelated applications during the class. You will be asked to leave if you violate this.
DO NOT turn on your handphone during class (at least keep it vibrate)
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Assessments
Class Participation 5
Lab Assignments/ Pop-up quiz 10
Lab Tests 10
Midterm 20
Group Project 15
Final Exam 40
TOTAL 100
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Course Outline1 Review of Database Concepts
Creating and Managing Tables2 Managing Constraints
Data Retrieval3 Single-row Functions
Date-time Functions4 Manipulating Data
Displaying Data from Multiple Tables5 Conditional Expression & Group Functions
Subqueries6 Lab Test 1
Views7 Sequence & Index
MidTerm Exam8 Mid Semester Break
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Course Outline
9 Introduction to PL/SQLWriting Executable Statements
10 Procedures11 Functions12 Decision Structures
LOOP13 Lab Test 2
Composite Data Types14 Exception Handling and Cursors
15 Group Project Demonstration
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Review of Database Concepts
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Objectives Review of Database Management
System (DBMS) and its functions Review of Client/Server Architecture Introduction to ORACLE Review of Relational database concepts Review of Integrity rules and types of
relationships
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Database Shared collection of logically related
data (and a description of this data), designed to meet the information needs of an organization.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Database
Shared collection – can be used simultaneously by many departments and users.
Logically related - comprises the important objects and the relationships between these objects.
Description of the data – the system catalog (meta-data) provides description of data to enable data independence.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
DBMS A software system that enables
users to define, create, and maintain the database and that provides controlled access to this database.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Database application program A software program that interacts
with the database by issuing an appropriate request (typically an SQL statement) to the DBMS.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Functions of a DBMS Data Storage, Retrieval, and Update. A User-Accessible Catalog. Transaction Support. Concurrency Control Services. Recovery Services.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Functions of a DBMS Authorization Services. Support for Data Communication. Integrity Services. Services to Promote Data
Independence. Utility Services.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Personal Databases
Typically maintained by the individual who owns it and uses it
Personal database management systems, such as Microsoft Access and Visual Fox Pro, are usually stored on a user’s desktop computer system or a client computer.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Personal Databases
A server is a computer that accepts and services requests from other computers, such as client computers.
A network is an infrastructure of hardware and software that enables computers to communicate with each other.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Personal DatabaseFile Server
( Database Files)
• Gets file requests from
clients• Sends files to
clients• Receives files
from clientsClient 1
( DBMS, Client Application)
• Sends file requests from clients
• Receives files from server
• Adds, deletes, updates data
• Sends files to clients
Client 2( DBMS, Client Application)
• Sends file requests from clients
• Receives files from server
• Adds, deletes, updates data
• Sends files to clients
NETWORK
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
ORACLE An Oracle database is a collection of data
treated as a unit. The purpose of a database is to store and retrieve related information.
A database server is the key to solving the problems of information management.
In general, a server reliably manages a large amount of data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
ORACLE A database server also prevents unauthorized
access and provides efficient solutions for failure recovery.
An Oracle database system can easily take advantage of distributed processing by using its client/server architecture. In this architecture, the database system is divided into two parts: a front-end or a client, and a back-end or a server.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
ORACLE The database has logical structures and
physical structures. Because the physical and logical structures are
separate, the physical storage of data can be managed without affecting the access to logical storage structures.
Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost effective way to manage information and applications.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Overview of Application Architecture
Client/Server Architecture Multiprocessing uses more than one
processor for a set of related jobs. Distributed processing:
reduces the load on a single processor concentrate on a subset of related tasks improving the performance and capabilities of the
system as a whole.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Client Server Architecture
Database Server
• Gets data requests from
clients• Adds, deletes, updates and filter
dataClient 1(Client
Application)• Sends data requests
• Receives results• Sends new data
Client 2(Client
Application)• Sends data requests
• Receives results• Sends new data
NETWORK
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Database Terminology Review
Define the following terms: Entity Entity Set Attribute Tuple Domain Key Null
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Supermarket
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Database Terminology Review
Define the following terms: Entity – A person, place, item, event or a ‘thing’. Entity Set – A collection of related entities. For example,
entity INVOICE contains information about invoices. Attribute – A characteristic that describes an entity. For
example, invoice number and invoice date are attributes of entity INVOICE.
Tuple – Another name for a row or a record. It contains attributes of an entity.
Domain – A set of all possible values for an attribute. Key – A minimal set of attributes, which uniquely defines an
entity in an entity set. Null – Unknown, unspecified, not known or not applicable
value.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Database Terminology Review
What are two rules of relational database?Two integrity of the relational database: Entity Integrity – Primary key attributes may not
be null. The primary key uniquely identifies an entity, so it cannot be null and it must have a unique value.
Referential Integrity – A foreign key may have a null value or it may have a value that exists as a primary key value in another table it references.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Database Terminology ReviewWhat are different types of keys?Types of keys: Primary key – A single attribute is used to uniquely
identify an entity in a table. Composite key – A combination of two or more
attributes is used as a unique identifier, also known as a composite primary key.
Surrogate key – An additional attribute is used in a table as a primary key instead of a composite key.
Foreign key – An attribute in a table that references a primary key attribute in another table.
Secondary key – A non-key attribute used in the search operation.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Database Terminology Review Describe different types of relationships with examples:
There are three types of relationships: One-to-one (1:1)
One-to-one relationship exists between two entity sets if an entity in the first entity set has only one matching entity in the second entity set, and vice versa. For example, a manager and a department.
One-to-many (1:M) One-to-many relationship exists between two entity sets if an entity in the first entity set has only one matching entity in the second entity set, but an entity in the second entity set has many matching entities in the first entity set. For example, an employee and a department.
Many-to-many ( M:M or M:N) Many-to-many relationship exists between two entity sets if an entity in the first entity set has many matching entities in the second entity set, and vice versa. For example, a student and a course.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Database Terminology Review
Define the following terms: Partial dependency Transitive dependency Normalization Data anomalies Cardinality
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Database Terminology ReviewDefine the following terms: Partial dependency – A non-key attribute in a table is
dependent on part of the composite key. Transitive dependency – A non-key attribute is dependent on
another non-key attribute. Normalization – A decomposition process to reduce data
dependencies and data anomalies. Data anomalies – A deletion anomaly that deletes information
about one entity while deleting information about another entity. An insertion anomaly that does not allow insertion of an entity unless information about another entity is known.
Cardinality – The lower and upper limit information about a relationship.
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Database Terminology ReviewDetermine the multiplicity for the following relationships:(a) Student and Course(b) President and Country(c) Staff and Dependent(d) Order and Product
1:MM:N
1:1M:N
International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems
Practice: ER DiagramSalam Sdn Bhd is a small company dealing with industrial applications of computers. The company delivers various products to its customers ranging from a single application program through to complete installation of hardware with customized software. The company employs several staff. Some staff are employed on long-term basis, and others are temporary staff.
Draw an Entity Relationship Diagram to model the above scenario