DDD Notes Final exam - Summary Database Design and Development PDF

Preview

Summary

Download DDD Notes Final exam - Summary Database Design and Development PDF

Description

Summary – Database Systems: A Practical Approach to Design, Implementation and Management

Chapter 1 – Introduction to databases 1.1 – Introduction    

Database = A shared collection of logically related data and a description of this data, designed to meet the information needs of an organisation DBMS = Database management system, manages and controls access to a database Database Application = Program that interacts with the database at some point of its execution Example of databases: Supermarket stock, Credit card transaction, Booking vacation, Local library, DVD renting, Many internet sites

1.2 – Traditional File-based systems 



File-based system o A collection of application programs that perform services for the end-users, such as the production of reports. o Each program defines and manages its own data. o Developed for efficient data access rather than use of manual sorting o Programming in 3GL is expensive to store and retrieve data o Data is structurally dependant o System is data dependant o Each application has to contain the data a structure as well as the data access mechanisms Limitations of File based approach o Separation and isolation of data o Data duplication = loss of data integrity and become inconsistent o Data dependence o Incompatible file formats = Structure of files are embedded in app programs hence dependant on that programming language o Fixed queries/proliferation of application programs

1.3 –Database Approach Factors of file-based limitations 1. Definition of data is embedded in application program and not separately stored (independent) 2. No control over access and manipulation of data beyond the application program Hence, solution was to separate it into two: Database and DBMS  The database o Holds operational data and its metadata  program data independence o Data abstraction: Changing internal definition of object won’t affect users of object if external definition is same. o Everything is logically related using entities, attributes, and relationships  DBMS o DDL (Data definition Language) allows specification of data types and structures and constraints on data in the database o DML (Data manipulation language): Insert, update, delete, retrieve data. o Query language (E.g. SQL = structure query language) 1

Summary – Database Systems: A Practical Approach to Design, Implementation and Management



Database application programs o Views: Gives users a unique or designed view of the database  Provide security as only certain data can be viewed by certain people  Customisation of appearance of database (reduce complexity)  Changing underlying database does not change view (program data independence DBMS components

    

Hardware: computer, mainframe, network, etc. Software: 3GL (3rd gen programming language) such as C, C++, C#, java or 4GL Data: Structure of database is called schema Procedures: Instructions and rules governing design and use of database People: People involved with system

1.4 – Roles in Database Environment   

 

Data Administrator: manage data resource Database administrator: physical realisation of database Database designers o Logical: Understanding of organisation data for business rules (entities/attributes). Conception design as well. o Physical: Mapping logical database design into table. Storage structure/access methods. Security measures Application developers End users

1.6 – DBMS Advantages and disadvantages              

Advantages Control of data redundancy Data consistency More information on same amount of data Sharing of data Improved data integrity Improved security Enforcement of standards Economy of scale Balance of conflicting requirements Improved data accessibility and responsiveness Increased productivity Improved maintenance through data independence Increased concurrency Improved backup and recovery

Chapter 2 – Database Environment 2

      

Disadvantages Complexity Size Cost of DBMS Additional hardware cost Conversion costs Performance Greater impact of failure

Summary – Database Systems: A Practical Approach to Design, Implementation and Management

2.1 – 3-level ANSI-SPARC Architecture ANSI: American National Standards Institute SPARC: Standards Planning and Requirements Committee Abstraction levels (of 3-levels of schemas)  External: Users perception of data (views)  Conceptual: Mapping and desired independence between external & Internal o Represents entities, attributes, relationships o Constraints on the data o Semantic information of data o Security and integrity information  Internal: DMBS and OS perception of data o Storage space allocation for data o Encryption and compression of data o Record description (data types) DBMS is responsible for mapping these 3 types of schemas Data Independence - Objective of 3-level architecture so upper levels are not affected by lower level changes  Logical: The immunity of the external schemas to changes in the conceptual schema.  Physical: The immunity of the conceptual schema to changes in the internal schema.

2.3 – Data Models and Conceptual Modelling 



3

Data model o An integrated collection of concepts for describing and manipulating data, relationships between data, and constraints on the data in an organization. o Purpose: represent data and allow it to be understood o Data model can be thought of as 3 components:  Structural: Rules to which database can be constructed  Manipulative: defines types of operation allowed on data  Integrity constraints: ensure data is accurate Data model fall into 3 categories o Object-based: Describe data at conceptual and external level  Uses concepts like: entities, attributes, relationships  E.g ER, Semantic, Function, O-O o Record-based: Describe data at conceptual and external level  No. of fixed format records  3 principal types model: Relational, network, hierarchical data model o Physical data: Describe data at internal level

Summary – Database Systems: A Practical Approach to Design, Implementation and Management

 E.g. Unifying model, Frame memory  Conceptual modelling  ‘heart of a database’ – Supports all external views and supported by internal schema  Logical modelling

2.4 – Functions of a DBMS 

4

Functions: o Data storage, retrieval, and update  Fundamental function o User accessible catalogue  Feature of ANSI SPARC is recognition of system catalogue (metadata) o Transaction support  Mechanism that will ensure that all updates corresponding to a given transaction are made or none are made  Consistent state > failed transaction = inconsistent > revert to last consistent state o Concurrency control services  Ensure database is correctly updated when multiple users are updating o Recovery services  E.g. transaction support: Database must be returned to consistent state o Authorization services  Only authorized users can access  Views: prevent sensitive data from being viewed o Support for data communication  Capable of integrating with communication software  Remote access o Integrity services  Data in the database and changes to data must follow certain rules  Database integrity: correctness/ consistency of data  Expressed with constraints o Services to promote data independence  Support independence of programs from actual database structure  Achieved through view/ subschema o Utility services  E.g. import facilities, load db from flat files  Monitor db usage/ operation  Statistic of performance  Indexing

Summary – Database Systems: A Practical Approach to Design, Implementation and Management

Chapter 3 – Database Architecture and the Web 3.1 – Multi-user DBMS Architectures 





5

Teleprocessing o Traditional architecture for multi user system: one computer as CPU and a number of terminals o Current industry: Trending towards downsizing o Rise to 2 architectures: File server & Client server File server architecture o A computer attached to a network with the primary purpose of providing shared storage for computer files such as documents, spreadsheets, images, and databases. o Processing distributed by a LAN. File server is simply a shared HDD

o Disadvantages:  Large network traffic  Full copy of DBMS needed on each work station  Concurrency, recovery, integrity control is more complex as there can be many DBMS/workstations accessing same files Client server architecture o Traditional, 2-tiers

Summary – Database Systems: A Practical Approach to Design, Implementation and Management

o Server provides resource, client processes o Separates the 4 components of business applications  Tier 1: presentation of data to user  Tier 2: Supply data services to the client





6

o Advantages:  Enable wider access to existing databases  Increased performance: only server needs CPU upgrade. Etc.  Reduced hardware cost  Communication cost reduced as client only sent request  Increased consistency: server handle integrity checks  Maps to open systems architecture naturally Three tier client server Architecture o Need for enterprise scalability from the 2-tier client server model o Layers:  User interface (client)  Business logic and data process (application server)  DBMS storing data of middle layer (database server) o Advantages over 2-tier client server  Less expensive as it is ‘thin’ client not ‘fat’  Maintenance is centralised  Added modularity for easier modification  Loading balance as core business logic is separated from database functions N-tier Architectures o 3 tier architecture can be expanded for better scalability and flexibility o Application server will handle a no. of complex issues  Concurrency  Network connection management  Database connection pooling  Providing access to all database server  Clustering support  Load balancing  Failover

Summary – Database Systems: A Practical Approach to Design, Implementation and Management



Legacy database support

Chapter 4 – The Relational Model 4.1 – Relational Model history 



First proposed by E. F. Codd’s seminal paper, 1970. o Objectives of relational model:  High Data independence  Substantial grounds for dealing with data semantics, consistency and redundancy problems  enable expansion of set oriented data manipulation languages. Prototype relational DBMS System R, late 1970’s o Led to development of SQL (now ISO/ standard) o Led to Production of commercial relational DBMS (DB2, SQL/DS, Oracle)

4.2 – Terminology         

Relation: Table with columns and rows Attribute: Column of a relation Domain: set of allowable values for one or more attributes Tuple: row of a relation Degree: Number of attributes of a relation Cardinality: number of tuples it contains Relational database: Collection of normalized relations with relation name Relation schema: Named relation defined by set of attribute and domain Relational database schema: Set of relation schemas with distinct name

Relation properties  Name is distinct from other relation names in the schema  Cell of relation has one single value  Each attribute name is distinct  Values of attribute are all from same domain  Each tuple is distinct and has no duplicate  Order of attribute has no significance  Order of tuples has no significance Relational keys  Super key – An attribute or set of attributes, that uniquely identifies a tuple within a relation  Candidate key – Super key such that no proper subset is a super key within the relation  Primary key – Candidate key selected to identify tuples of specific relation  Foreign key – Attribute within relation that matches candidate key of another relation

7

Summary – Database Systems: A Practical Approach to Design, Implementation and Management

4.3 – Integrity Constraints    

Domain constraints form restriction on set of values allowed for attributes of relation o Null: Represents a value for an attribute that is currently unknown or N/A Entity integrity: In base relation, no attribute of primary can be null Referential integrity: If foreign key exists in relation, it must match value of candidate key value in home relation or must be null General constraints: Additional rules specified by user or DA

4.4 – Views 

8

Purpose: o Flexible security mechanism by hiding parts of database from certain users o Permits user to access data in way that is customised to needs o Simplifies complex operation

Summary – Database Systems: A Practical Approach to Design, Implementation and Management

Chapter 5 – Relational Algebra and Relational Calculus 5.1.1 – Unary Operations

E.g.

E.g.

5.1.2 – Set Operations

E.g.

E.g.

9

Summary – Database Systems: A Practical Approach to Design, Implementation and Management

E.g.

E.g.

5.1.3 – Join Operations

E.g.

10

Summary – Database Systems: A Practical Approach to Design, Implementation and Management

E.g.

E.g.

E.g.

11

Summary – Database Systems: A Practical Approach to Design, Implementation and Management

5.1.4 – Join Operations

E.g.

12

Summary – Database Systems: A Practical Approach to Design, Implementation and Management

14.4 – Functional Dependencies 

 



13

Functional dependency = Describes the relationship between attributes in a relation. For example, if A and B are attributes of relation R, B is functionally dependent on a (denoted A ® B), if each value of a is associated with exactly one value of B. ( A and B may each consist of one or more attributes.) Determinant = Refers to the attribute, or group of attributes, on the left-hand side of the arrow of a functional dependency. Full functional dependency = Indicates that if A and B are attributes of a relation, B is fully functionally dependent on A if B is functionally dependent on a, but not on any proper subset of A. Transitive dependency = A condition where A, B, and C are attributes of a relation such that if A ® B and B ® C, then C is transitively dependent on a via B (provided that a is not functionally dependent on B or C)....