Summary
Summary In short, ALL database lectures
- Course
- Institution
I passed this exam with a 9! I hope you too later;)
[Show more]
Seller
Follow
gideonrouwendaal
Reviews received
Content preview
Part 1: IntroductionA database (DB) is a collection of data with: a certain logical structure, a specific semantics and a
specific group of users.
A database management system (DBMS) allows us to manage databases. It allows us to:
- create modify and manipulate a database
- query (retrieve) the data using a query language
- support persistent storage of large amounts of data
- enable durability and recovery from failure
- control access to the data by many users in parallel
o without unexpected interactions among users (isolation)
o actions on the data should never be partial (atomicity)
Why not just store data in files? No query language, weak logical structure (limited to directories),
no efficient access, no or limited protection from data loss, and no access control for parallel
manipulation of data.
Most DBMS follow the ANSI SPARC Architecture. Motivation behind the structure:
- data independence
o logical view on the data independent of physical storage
o user interacts with a simple view on the data
o behind the scenes (invisible for the user) are complex storage structures that allow
rapid access and manipulation
- Avoidance of duplication
o Different views on the same database
▪ For different users or different applications
▪ Hiding parts of the data for privacy or security
This is achieved by ANSI SPARC Architecture. There are 3 levels. Different applications might use
different views. Data stored once at physical level (good for consistency).
To ensure logical and physical data independence, use 3 different views:
- Physical level
o How the data is stored
o Disk pages, index structures, byte layout, record order
- Logical level (conceptual schema)
o Describes data stored in the database and
o Relations among the data
- View level
, o Application programs hide details of data types
o Hide information for privacy or security
Logical data independence is the ability to modify the logical schema without breaking existing
applications (applications access the views, not the logical database). E.g. add columns. Physical data
independence is the ability to modify the physical schema without changing the logical schema (e.g.
a change in workload might cause the need for different indexing structures, different database
engine, distributing the database on multiple machines). E.g. from 100 users to 1000 users
In a Relational model, the view and logical level represents data as relationships or tables. In the
pure relational model, a table is a set of tuples: has no duplicate tuples (rows), and no order on the
tuples. For example, a set of 4 tuples (4 columns). Representing relations as tables (compared to
pure relational model) is that tables allow to have multiple rows, and the table suggest that there is
an order, not the case with pure relational model.
Database schema (header, title and column names): structure of the database, that is, relatoions +
constraints. Example: Customers(id, name, street, city) and Accounts(depositor → customers(id),
accountnr).
Database instance (rows of data): actual content (‘content’) of the database at some moment.
Structured Query Language (SQL). Motivation for Database Management Systems: High-level
declarative query languages: query tells what you want, independent of storage structure, and
efficient data access. Declarative query languages: describe what information is sought, but do not
prescribe how to retrieve the desired information.
Imperative language: explicit control, implicit logic. Declarative language: implicit control and
explicit logic. In relational databases, the main language is SQL. SQL is a declarative data
manipulation language. The user describes conditions the requested data is required to fulfil.
Declarative languages are more concise than imperative languages: less expensive program
development and easier maintenance. Database system will optimize the query: decides how to
execute the query as fast as possible, and the users (usually) do not need to think about efficiency.
Database management systems provide well-defined data models and data integrity constraints,
namely relational model, meta language for describing (data, data relationships and data
constraints). Primary key constraint on x: only unique x’s. Foreign key constraint id should match
the accountnnr. Overview of types of constraints:
- Data types, constrained data types, e.g. numeric
- Columns constraints, e.g. unique, nullability, counter…
- Check constraints: logical expression for domain integrity
This creates a table called solved, where id automatically
increment each time. The id uniquely identifies the row (primary key), name is a string of max 40 and
it cannot be null (have to be filled in), homework is a decimal number (2), same with points and that
one cannot be smaller than 10.
,Databases allow concurrent access & transactions (multiple users and transactions with ACID
properties). A transaction is a sequence of operations that performs a single logical function in a
database application. Database management system ensures ACID properties:
- Atomicity: transaction executes full (commit) or not at all (abort)
- Consistency: database remains in a consistent state where all integrity constraints hold
- Isolation: multiple users can modify the database at the same time, but will not see each
others partial actions
- Durability: once a transaction is committed successfully the modified data is persistent,
regardless of disk crashes.
The main tool in Designing Database Schemes is the Entity Relationship Model (ER). The entities are
the objects (e.g. customers/bank accounts…). There is a relationship between entities (e.g. account
27 is held by Gid). UML Class diagrams can also be used. This is similar to ER, but the entities are
now classes and the relationships are now associations. Conceptual design is usually converted to
the relational model.
Summary of why database management?
- Data independence (logical view on the data independent of physical storage
- Avoidance of duplication (different views on the same database)
- High-level declarative query language (what, not how, efficient data access, automatic
query optimization)
- Data models & data integrity (consistency) (transactions with ACID properties)
- Persistent storage, safety and high availability (safety against failure, backup/restore)
- Scalability (data could by much larger than main memory, indexing and scalable algorithms)
- Security (user permission management)
Part 2: Relational Model
The value null indicates that the field is empty.
Database schemas describe the structure of the database. Table entries are values that conform to
some data type. Examples of SQL data types:
- Strings
o varchar(n) – strings of up to n characters (n<=65535)
o (long)text – strings up to 4GB
- Numbers
o bit, int, float
o numeric(p,s) – decimal number p-s digits . s-digits (p in total, s after the dot)
- Binary data, e.g. blob
- Date and time, e.g. date, time, datetime, timestamp
Available data types depend on the database management system, and the supported version of the
SQL standard. Every datatype D comes with a particular domain (dom(D)). Dom(D) is the set of
possible values. E.g. dom(numeric(2,0)) = {-99, …, 99}. SQL allows to define application-specific
domains as subsets of standard data types. For example: “create domain ExampleDomain as
numeric(0,2)”. Can even add constraints: “create ExampleDomain as numeric(2,0) check(value > 0)”.
Domains are useful to document that 2 columns represent the same kind of objects and that
comparisons are meaningful.
, A Relations Schema s (of a single relation) defines:
- A (finite) sequence A1,..,An of distinct attribute names
- For each attribute Ai a data type (or domain) Di
A relation schema can be written as: s = (A1 : D1,…,An : Dn)
Relation schemas are important to understand what data is stored in the database. How to
communicate schemas from human to human? Create table statement is not handy, therefore: if the
column data types are not important, we can write Exercises(category, number, topic, maxPoints), or
a sketch of the table header.
A relational database schema S defines:
- A finite set of relation names {R1,…,Rm}
- A relation schema schema(Ri) for every relation Ri
- A set of integrity constraints C (defined later)
In summary, S = ({R1,..,Rm}, schema, C)
The database states are the actual content at a certain moment. In the relational model, tuples are
used to formalize table rows.
Given a tuple t, we write t.Ai for the value of
column Ai, so for example (‘exam’, 1, ‘SQL’, 14).topic = ‘SQL’.
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller gideonrouwendaal. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $16.72. You're not tied to anything after your purchase.
Can Stuvia be trusted?
4.6 stars on Google & Trustpilot (+1000 reviews)
66579 documents were sold in the last 30 days
Founded in 2010, the go-to place to buy study notes for 14 years now
Recently viewed by you
