INFS1603 12 Course Review rev07 PDF

Preview

Summary

Download INFS1603 12 Course Review rev07 PDF

Description

© Dr. Daniel Schlagwein or the respective right holders. All rights reserved.

UNSW Business School | Information Systems and Technology Management

INFS1603 Business Databases

W12: Course Review Dr. Daniel Schlagwein

Recap: W11 Learnings q Database development § Information systems development overview § Software development lifecylce (SDLC) § Database development lifecycle (DBLC) § Interaction between SDLC and DBLC q Database Administrator (DBA) § DBA vs. DA § DBA tasks § DBA ethics

2

Plan: W12 Learnings q Course Review: § Business Rules § Conceptual Model § Relation Model § Normalization § RDBMS/SQL § OO Modelling

3

DCL DDL

DBMS Language

DML

M

Database' Systems

Relational

uses

1 M

RDB

M HDB

d relatesBto

DSS

Information System

d

MIS

TPS

4

Model

d Logical

Physical

NDB

1

OO

Conceptual

Relational Calculus

manipulates

1

ER

Relational Algebra

d

Database Design: Overview The processes that we follow when designing a database for an organization: 1) We gather business requirements from the organization. 2) We develop a conceptual model using ER modelling technique. 3) We convert the conceptual model (ER model) to a relational model, a set of relations. 4) We normalize the relation model (relations) to remove any anomalies and convert to internal model. 5) We physically implement this internal model in a database by creating a table for each normalized relations.

5

Conceptual Model What is an conceptual model? q A conceptual data model is a representation of organizational data. q ER modelling is common modelling technique. q The result of ERM is an ER model: a detailed, logical representation of the data for an organization or for a business area. What is shown in an ER model? q An ER model is normally expressed as an ER diagram, which is a graphical representation of an ER model. q The ER model is expressed in terms of entities in the business environment. q The ER model also shows the relationships (associations) among those entities. q The ER model also shows attributes of both the entities and their relationships Note: In the exam, solve all ER questions without supertype/subtype structures (unless explicitly asked for)!

6

Relational Model What is a relation a model? q A relational model represents data in relations. q A relation can be thought of (and implemented as) a two dimensional table. q The name of a relation and the set of attributes for a relation is called the relational schema. What is shown in a relation model? q A relational model shows relations, the name and structure of a two dimensional table. q A relation model shows attributes, the names of the columns of relations. q A relation model shows tuples, the rows of relations.

7

Normalization What is normalization? q Normalization is a process for converting complex data structures into simple data structures. This can be accomplished in stages. What is the outcome of normalization? q First normal form (1NF): Any repeating data have been removed, so there is a single value at the intersection of each row and column of the table. q Second normal form (2NF): Non-key attributes require the whole key for identification. q Third normal form (3NF): Non-key attributes do not depend on other non-key data elements (which is called transitive dependencies). q We usually normalize to 3NF, which is an industry standard.

8

Relational Languages What is the “theory” behind relational databases? q Relational algebra and relational calculus are defined by Codd (1971) as the basis for relational languages. Both languages are not very user friendly languages. q Relational algebra operations can be classified in three main categories: § Union, Intersection & Difference: set operations. § Selection & Projection: remove parts of a relation. § Cartesian Product & Join: operations combine the tuples of two relations.

9

Relational Languages What is the practice of relational databases? q The RDBMS – for exampleOracle – provides data access via a query language. q The RDBMS’s query language – most commonly SQL – contains three components: § Data Definition Language (DDL) is used to specify the database schema or modify an existing one. § Data Manipulation Language (DML) is used to manipulate the data. § Data Control Language (DCL) is used for controlling the data.

10

SQL DML ISO SQL standard uses the terms tables, columns, and rows. q SELECT clause tell which attributes of the tuples matching the condition are produces as part of the answer. q FROM clause gives the names of relation(s). q WHERE clause is a condition which tuples must satisfy in order to match the query. SELECT FROM [WHERE ]

11

Object Modelling Concepts q The focus in the OO approach is to develop systems that are similar to the real world. q Classes q OO modelling has a set of constructs and terms: § Objects § Classes § Encapsulation: attributes and operations in the same object § Inheritance: child classes inherit attributes and § Polymorphism: operations can be implemented differently

12

Object Modelling Concepts Class'Diagram

Person Name DofB Address Phone Change_address Change_phone

Object'Diagram

Ms.BPink:BPerson Name:B Ms.BPink DofB: 1/1/1992 Address:BBBBUNSWBDormB5 Phone:BBBBBBB(02)B5555B5555

13

ClassBname ListBofBattributes

ListBofBoperationsB(actions

ObjectBnameB:BclassBname

DBLC & SDLC

14

Database Administration q When a new DBMS is introduced to an organization, three important aspects have to be addressed. § Technological § Managerial § Cultural q The person responsible for the control of database is called Database Administrator (DBA).

15

DBA q The role and position of DBA may vary in companies. q Some of the larger corporations make a distinction between DA (data administrator) and DBA (database administrator) q DA: A high-level function that is responsible for the overall management of data resources in an organization, including maintaining corporate-wide definitions and standards. q DBA: A technical function that is responsible for physical database design and for dealing with technical issues such as security enforcement, database performance and backup and recovery.

16

Questions

17

MyExperience

18

Exam Advise

19

Within-Group Peer Review

20

Recap: W12 Learnings q Course Review: § Business Rules § Conceptual Model § Relation Model § Normalization § RDBMS/SQL § OO Modelling q MyExperience q Exam Advise q ( Within-Group Peer Review )

21

source: murketing.com

22...