Samenvatting
Summary Samengevat, ALLE lectures van databases
- Instelling
- Vrije Universiteit Amsterdam (VU)
Dit tentamen gehaald met een 9! Ik hoop jij ook straks ;)
[Meer zien]Voorbeeld 4 van de 34 pagina's
Voorbeeld 4 van de 34 pagina's
In winkelwagengesponsord bericht van onze partner
Voorbeeld van de inhoud
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’.
Voordelen van het kopen van samenvattingen bij Stuvia op een rij:
Verzekerd van kwaliteit door reviews
Stuvia-klanten hebben meer dan 700.000 samenvattingen beoordeeld. Zo weet je zeker dat je de beste documenten koopt!
Snel en makkelijk kopen
Je betaalt supersnel en eenmalig met iDeal, creditcard of Stuvia-tegoed voor de samenvatting. Zonder lidmaatschap.
Focus op de essentie
Samenvattingen worden geschreven voor en door anderen. Daarom zijn de samenvattingen altijd betrouwbaar en actueel. Zo kom je snel tot de kern!
Veelgestelde vragen
Wat krijg ik als ik dit document koop?
Je krijgt een PDF, die direct beschikbaar is na je aankoop. Het gekochte document is altijd, overal en oneindig toegankelijk via je profiel.
Tevredenheidsgarantie: hoe werkt dat?
Onze tevredenheidsgarantie zorgt ervoor dat je altijd een studiedocument vindt dat goed bij je past. Je vult een formulier in en onze klantenservice regelt de rest.
Van wie koop ik deze samenvatting?
Stuvia is een marktplaats, je koop dit document dus niet van ons, maar van verkoper gideonrouwendaal. Stuvia faciliteert de betaling aan de verkoper.
Zit ik meteen vast aan een abonnement?
Nee, je koopt alleen deze samenvatting voor €15,49. Je zit daarna nergens aan vast.
Is Stuvia te vertrouwen?
4,6 sterren op Google & Trustpilot (+1000 reviews)
Afgelopen 30 dagen zijn er 73918 samenvattingen verkocht
Opgericht in 2010, al 14 jaar dé plek om samenvattingen te kopen