Constraints are there to impose restrictions on what data is allowed in the database. The basic E/R model introduces us to 2 types of constraints:

• KEYS
• Multiplicities

The relational model allows us to be more flexible. It imposes bounds on attributes. For now, we are able to force an attribute to be:

• non null
• of a specific domain (integer, string, etc)

But what if we need to express the situation of a student that can only take some courses if his GPA > 3.0? What if, in order to be a teacher assistant, you need to have successfully passed that course with an A or you need to have a 3.0 of cumulative GPA? By introducing the SQL keyword CHECK, we introduce 2 new notions:

• attribute-based checks : fancy name to mention that we are imposing a constraint on a column
• tuple-based checks: imposing constraints on rows.

We are introducing checks (and in general, constraints), for...

Using the next common keywords will return a column with only one row. They are called aggregate functions:

• 1) COUNT
• 2) AVG
• 3) SUM
• 4) MIN/MAX

1) COUNT : returns the number of rows matching a specific criteria.

• SELECT COUNT * FROM Skaters
• SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders WHERE CustomerID=7;
• SELECT COUNT DISTINCT(CustomerID) FROM Orders : selects the number of distinct customers.

2) AVG : returns the average value of a column

• SELECT AVG(Price) FROM Products: the average price of the products column.

3) SUM : returns the sum value of a column

• SELECT SUM(Quantity) FROM OrderDetails : sum of all quantity fields for the “OrderDetails” table.

4) MAX/MIN : returns the largest/lowest value of the selected column.

• SELECT MAX(Price) AS HighestPrice FROM Products;
• SELECT MIN(Price) AS SmallestOrderPrice FROM Products;

Let’s investigate...

This part won’t cover the notions of passing from relational algebra to SQL since this has already been covered in the previous post. In this section, we will start by introducing a main overview of a basic SQL query. Then, we structure this overview into 10 keywords and 6 short notions commonly used in SQL :

• 1) When to use * (star)
• 2) Operators
• 3) The DISTINCT keyword
• 4) Renaming
• 5) Evaluations within queries
• 6) Constraints
• 7) The ORDER BY keyword
• 8) Range variables
  
• 9) The JOIN keyword
• 10) The UNION keyword
• 11) The INTERSECT keyword
• 12) The EXCEPT keyword
• 13) The ALL keyword : sets & multisets
• 14) The IN keyword
• 15) The EXISTS keyword
• 16) The ALL/ANY/SOME keywords : quantifiers
  

The most basic form of a SQL query looks like the following :

• SELECT CustomerName FROM Customers WHERE Country=“Mexico”

More generally, we’ll have:

• SELECT (what columns we want to keep?) FROM...

Let’s start by understanding what relational algebra is:

• it is a query language (not a programming language)
• takes instances of relations as input (basically rows from a table, also called tuples)
• yields instances of relations as output
• uses operators to perform queries (unary - applied on a single relation, or binary - applied on two relations).
• its role? define database operations in terms of algebraic expressions

A query language allows manipulation and retrieval of data from a database. It is useful to explain how a SQL query is executed internally.

Understanding relational algebra is basically understanding SQL. Some basic operations from relational algebra are:

• Select (σ)
• Project (∏)
• Rename (ρ)
• Union (∪)
• Intersection (∩)
• Set difference (-)
• Cross product (X) - also called Cartesian product or Cross join
• Condition join - also called Theta-join
• Equi-join

The select...

The entity-relationship model described in the previous lecture is a diagram. The main purpose is to clearly picture the constraints of the database to someone that is not necessarily coming from a computer science field.

The model that’s the most popular in Database Systems is the relational model: a representation of structuring data through relations between rows and columns. As seen in Lecture 1, relational database is composed by:

• schema : column headers ( same as attribute names)
• instances : the rows.

The schema for the database Students can be represented as Students(studID:string, name:string, login:string, faculty:string, gpa:real). It must contain:

• name of the relation
• set of attributes
• domain/type
  

The schema of the database is defined by the DDL : data definition language. It is used mainly to create and destroy databases and databases objects. The main operations...

@dandancrisan

This page will cover the following keywords : E/R diagram, E/R schema, entities, their attributes, entity sets, keys, ISA hierarchies, overlap constraints, covering constraints (specialization, generalization), relationships, relationship sets, key-constraints (many-to-many, one-to-many, many-to-one, one-to-one), participation constraints (partial / total). You should be able to explain them in your own words at the end of this page.

Before we start, let’s clarify what “Entity-Relationship Model” is. In a nutshell, the ERM is a “GUI for DBs” : a graphical representation of the requirements for a database. Within this lecture, we will notice it under different names, but they all mean the same:

• ER diagram
• ER schema
• semantic schema

A little confusion might occur between Entity-Relationship schema and relational schema. The relational schema refers to the...

Let’s start with some new vocabulary and acronyms:

• DBMS : Database Management System
• Data model : concepts describing a collection of data (example: relational data model, object-oriented data model).
• Schema : characteristics and properties of that collection of data (the columns in a relational database and their type - int, string, etc.).
• Instance : a particular collection of data (the rows in a relational database).

As data models, we can have :

• Relational : the most popular one, basically a table with rows and columns (MySQL);
• Semi-structured : relational but with XML. (NoSQL databases: CouchDB, MongoDB);
• Object-oriented (OO) : relational but with an OO-interface;
• Key-value : simple interface + scalable (Cassandra/Hbase)

The main advantages of the relational model are :

• simplicity and power
• strong mathematics
• accessing data can be done using powerful...

Here is a way to install Windows 10 from an USB key instead of a CD. What you need is:

• a PC running Windows
• an empty memory key with at least 8 GB
• the PC on which you want to install Windows 10.

The first step is to make the USB key ready for the installation.

1) Plug in your USB key into your PC that already has a Windows OS on it.

2) Open Command Prompt as an administrator: right click on the CMD icon and choose Run as administrator.

3) Type diskpart and press enter.

4) In the diskpart command prompt, type list disk and press enter.

5) Select the disk that has the corresponding size to your USB. Say that is disk 2, then type select disk 2.

6) Type clean and press enter.

7) Type create partition primary and press enter.

8) Type format fs=ntfs quick and press enter.

9) Type active and press enter.

10) Type assign and press enter.

11) Type list volume and press enter...

The OS provides 4 major functions to manage the virtual memory:

• Replacement : which page should be deleted to make room for other pages
• Allocation : how many pages per process should be allocated
• Fetching : when bring a page into memory
• Placement : where in memory we should place a page

Memory management has 2 purposes :

• memory protection
• memory sharing

Some challenges need to be considered while sharing memory :

• Transparency : processes must co-exist
• Safety
• Efficiency
• Relocation : program should run in different memory locations

To co-exist, memory is divided into categories:

• program & data
• read-only & read-write
• addresses & data

To execute the program, the system follows some procedures:

• compile (translate) : creates the object code
• link : combine into executable code
• load : copy code into memory
• execute : dynamic memory allocation

The main challenges with the...

Facebook just bought Oculus VR. You bought the headset. You bought a share of the future. Now how do you start handling it?

“The incredible thing about the technology is that you feel like you’re actually present in another place with other people. People who try it say it’s different from anything they’ve ever experienced in their lives.” - Mark Zuckerberg (CEO of Facebook)

First, let’s see what’s in the box:

• 1 headset
• 1 connection box
• 3 pairs of lenses
  
• 1 power supply
• 1 DVI cable
• 1 HDMI cable
• Adapters for all regions…

Let’s getting started with the connection box.

You notice that it has 4 parts:

• 1) HDMI
• 2) DVI
• 3) USB
• 4) power

At this point it’s really plug and play. Literally.

• Connect either the HDMI or the DVI port of your computer to the HDMI or the DVI port of the connection box;
• Connect the USB port of your computer with the USB port of the connection box.
• Plug in...