Introduction to Relational Databases
text varchar char boolean true false null
T_______ - character strings of any length V_________ [(X)] - a maximum of n characters C________ [(X)] - a fixed-length string of n characters B ______ - can only take three states, T______, F______, N______
foreign
a 1:N-relationship can be implemented with one for_______________ key in the table that has at most one for_______________ entity associated
ALTER TABLE professors ADD CONSTRAINT professors_fkey FOREIGN KEY (university_id) REFERENCES universities (id);
ADD a column called university_id in table PROFESSORS as an integer ALTER TABLE a ADD CONSTRAINT a_fkey FOREIGN KEY (b_id) REFERENCES b (id); Add a foreign key on university_id column in professors that references the id column in universities. Name this foreign key professors_fkey.
ALTER TABLE organizations RENAME COLUMN organization TO id; ALTER TABLE organizations ADD CONSTRAINT organization_pk PRIMARY KEY (id);
ALTER TABLE table_name ADD CONSTRAINT some_name PRIMARY KEY (column_name) Rename the organization column to id in organizations. Make id a primary key and name it organization_pk.
ALTER TABLE professors ADD COLUMN id serial
Add a new column id with data type serial to the professors table.
ALTER TABLE professors ALTER COLUMN firstname SET NOT NULL; ALTER TABLE professors ALTER COLUMN lastname SET NOT NULL
Add a not-null constraint for the firstname column of the professors table. Use ALTER COLUMN Add a not-null constraint for the lastname column. SET NOT NULL
ALTER TABLE affiliations ADD COLUMN professor_id integer REFERENCES professors (id);
Add a professor_id column with integer data type to affiliations table, and declare it to be a foreign key that references the id column in professors.
ALTER TABLE affiliations ADD CONSTRAINT affiliations_organization_fkey FOREIGN KEY(organization_id) REFERENCES organizations (id);
Add affiliations_organization_fkey constraint with foreign key organization_id that references the id of organizations
ALTER TABLE affiliations ADD CONSTRAINT affiliations_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE;
Altering a key constraint doesn't work with ALTER COLUMN. Instead, you have to delete the key constraint and then add a new one with a different ON DELETE behavior. Add a new foreign key constraint from affiliations to organizations which cascades deletion The constraint should be affiliations.organization_id_fkey as foreign key and organization_id that references organizations table with id column.
combine columns
Another strategy for creating a surrogate key is to com______________ two existing col________________ into a new one.
UPDATE universities SET id = CONCAT(university, university_city);
Concatenate columns university and university_city into id using an UPDATE table_name SET column_name = ... query and the CONCAT() function.
consistency form
Constraints give you consis_______________, meaning that a row in a certain table has exactly the same fo___________ as the next row, and so forth. All in all, they help to solve a lot of data quality issues.
CREATE TABLE students ( last_name varchar(128) NOT NULL, ssn integer PRIMARY KEY, phone_no char (12) );
Create a student table that has the following: a last name consisting of up to 128 characters (this cannot contain a missing value), a unique social security number consisting of only integers, a phone number of fixed length 12, consisting of numbers and characters (but some students don't have one). Add a PRIMARY KEY for the social security number ssn.
CREATE TABLE professors ( firstname text, lastname text ); CREATE TABLE universities ( university_shortname text, university text, university_city text ALTER TABLE professors ADD COLUMN university_shortname text;
Create a table professors with two text columns: firstname and lastname. Create a table universities with three text columns: university_shortname, university, and university_city. Alter professors to add the text column university_shortname.
ALTER TABLE affiliations RENAME COLUMN organisation TO organization; ALTER TABLE affiliations DROP COLUMN university_shortname;
Create table affiliations and add columns organisation and university_shortname Rename the organisation column to organization in affiliations. Delete the university_shortname column in affiliations.
ALTER TABLE affiliations DROP COLUMN firstname
Drop the firstname and lastname columns from the affiliations table.
ALTER TABLE professors ALTER COLUMN university_shortname TYPE char(4);
Example: ALTER TABLE table_name ALTER COLUMN column_name TYPE char(10) Now specify a fixed-length character type with the correct length of 4 for university_shortname for the professors
ALTER TABLE organizations ADD CONSTRAINT organization_unq UNIQUE(organization)
First create a table called organizations and create a column named organization Add a UNIQUE constraint to the organization column in organizations. Give it the name organization_unq.
foreign primary domain data foreign value primary foreign
For_____________ keys are designated columns that point to a pr_____________ key of another table. There are some restrictions for foreign keys. First, the dom_________ and the da_________ type must be the same as one of the primary key. Secondly, only foreign key values are allowed that exist as values in the primary key of the referenced table. This is the actual foreign key constraint, also called "referential integrity". Each fore__________ key val_____________ must exist in the pri________________ key of the other table. For_____________ keys are not actual keys.
UPDATE affiliations SET professor_id = professors.id FROM professors WHERE affiliations.firstname = professors.firstname AND affiliations.lastname = professors.lastname;
Here's a way to update columns of a table based on values in another table: UPDATE table_a SET column_to_update = table_b.column_to_update_from FROM table_b WHERE condition1 AND condition2 AND ...; Update the affiliations table and update the professor_id column with the corresponding value of the id column in professors from the professors table and where affiliations first and last name = professors first and lastname. "Corresponding" means rows in professors where the firstname and lastname are identical to the ones in affiliations.
number string
If you define an artificial primary key, ideally consisting of a unique num________ or str_________, you can be sure that this number stays the same for each record. Other attributes might change, but the primary key always has the same value for a given record.
delete action error
If you specify a foreign key on a column, you can actually tell the database system what should happen if an entry in the referenced table is deleted. By default, the "ON DE___________ NO AC______________" keyword is automatically appended to a foreign key definition, like in the example here. This means that if you try to delete a record in table B which is referenced from table A, the system will throw an er____________.
one candidate
In the end, there can only be on___________key for the table, which has to be chosen from the cand_____________ keys.
INSERT INTO professors (firstname, lastname, university_shortname) VALUES ("john","pong","unit") INSERT INTO universities SELECT DISTINCT university_shortname FROM professors
Insert values john, pong, unit to the firstname, lastname, university_shortname of PROFESSORS table. Insert into universities from professors and insert columns university_shortname Make sure to add SELECT DISTINCT
integrity attribute key keys uniquely identify referential
Inte__________ constraints can roughly be divided into three types. The most simple ones are probably the so-called attr_____________ constraints. For example, a certain attribute, represented through a database column, could have the integer data type, allowing only for integers to be stored in this column. They'll be the subject of this chapter. Secondly, there are so-called ke__________ constraints. Primary k_________, for example, uni_____________ iden__________ each record, or row, of a database table. They'll be discussed in the next chapter. Lastly, there are refe_______________ integrity constraints. In short, they glue different database tables together.
casts
It is impossible to calculate a product from an integer *and* a text column. The text column "wind_speed" may store numbers, but PostgreSQL doesn't know how to use text in a calculation. The solution for this is type ca________, that is, on-the-fly type conversions. In this case, you can use the "CA_______" function, followed by the column name, the AS keyword, and the desired data type, and PostgreSQL will turn "wind_speed" into an integer right before the calculation.
2 entities
N:M-relationships contains ____(#) foreign keys that point to both connected en______________.
unknown exist apply meaning
NULL" can mean a couple of things, for example, that the value is un_______________, or does not ex____________ at all. It can also be possible that a value does not ap_______ to the column. One important take away is that two "NULL" values must not have the same me____________
duplicate null few
Primary keys need to be defined on columns that don't accept dup_____________ or nu_____________ values. Primary keys consist of as fe__________columns as possible!
deleted insert
Referential integrity can be violated in two ways. Let's say table A references table B. So if a record in table B that is already referenced from table A is del_____________, you have a violation. On the other hand, if you try to ins____________ a record in table A that refers to something that does not exist in table B, you also violate the principle.
existing constraint 2 foreign
Referential integrity states that a record referencing another record in another table must always refer to an exi______________ record. In other words: A record in table A cannot point to a record in table B that does not exist. Referential integrity is a cons______________ that always concerns _______(#) tables, and is enforced through for______________ keys, as you've seen in the previous lessons of this chapter.
ALTER TABLE universities RENAME COLUMN university_shortname TO id; ALTER TABLE universities ADD CONSTRAINT university_pk PRIMARY KEY(id);
Rename the university_shortname column to id in universities. Make id a primary key and name it university_pk.
surrogate native primary
Sur_______________ keys are sort of an artificial primary key. In other words, they are not based on a nat___________ column in your data, but on a column that just exists for the sake of having a pri______________ key.
cascade deletion delete deletion
The "CAS_____________" option, which will first allow the del_________ of the record in table B, and then will automatically de______________ all referencing records in table A. So that del___________ is cas______________.
foreign
The "SET NULL" option will set the value of the for___________ key for this record to "NULL".
incrementing
There's a special data type in PostgreSQL that allows the addition of auto-incr__________________ numbers to an existing table: the "serial" type. It is specified just like any other data type. Once you add a column with the "serial" type, all the records in your table will be numbered.
entity-relationship diagram entity columns
This is called an en_____ - rela_______________ dia_____________ The square is called the en______ type and the circles are called col__________.
attributes superkey
Typically a database table has an attr___________, or a combination of multiple attr____________, whose values are unique across the whole table. Such attr___________ identify a record uniquely. Normally, a table, as a whole, only contains unique records, meaning that the combination of all attr________________ is a ke__________ in itself. However, it's not called a ke__________, but a supe________ke_____, if attr___________from that combination can be removed, and the attri________________ still uniquely identify records.
numeric integer
num______ 3.132 int_________ 24123213
ALTER TABLE universities ADD CONSTRAINT university_shortname_unq UNIQUE(university_shortname);
CREATE TABLE table_name ( column_name UNIQUE ); If you want to add a unique constraint to an existing table, you do it like that: ALTER TABLE table_name ADD CONSTRAINT some_name UNIQUE(column_name); Add a unique constraint to the university_shortname column in universities. Give it the name university_shortname_unq.
ALTER TABLE prfoessors ALTER COLUMN firstname TYPE varchar(64);
Change the professors table and change the firstname column type to varchar(64)
3
How many entity types are there in this picture?
