Database#

Postgresql#

21 Aug 2024

List schema: SELECT schema_name FROM information_schema.schemata

General#

21 Aug 2024

Table vs Relation#

For instance, a SQL table can potentially contain duplicate rows, whereas a true relation cannot contain duplicate rows that we call tuples. Similarly, representation as a table implies a particular ordering to the rows and columns, whereas a relation is explicitly unordered. Table Database)

Naming convention#

• Table and Model use singular learn-sql-naming-conventions

Database Design with UML and SQL notes#

4 Jul 2024

Reference: Database design with UML and SQL, 4th edition

It’s important to recognize that defining schemes or domains as sets of elements automatically tells us a lot more about them, from the mathematical properties of sets.

• A set cannot contain duplicate elements.
• The elements in a set are unordered.
• Given a set, rules can be developed to determine when an element can be included or excluded from it.
• We can define subsets of them; for example, we can display only a selected set of attributes from a scheme, or we can limit the domain of an attribute to a specific range of values.
• They may be manipulated with the usual set operators: union, intersection, etc. For example, given a set of customers from one company and a second set of customers from a different company, the intersection of these gives the set of customers in common to both companies.

Key points:

• A database table is simply a collection of zero or more rows. This follows from the relational model definition of a relation as a set of tuples over the same scheme.
• In an object oriented (OO) programming language, an object is the instance of a class. Methods can be invoked on an object to inspect and manipulate its properties. Unlike in OO systems, traditional relational databases store information as records of data, not as objects.
• Since each tuple in a relation must be unique, no two tuples can have exactly the same values for every one of their attributes, that is, there can be no duplicate tuples in a relation. Unfortunately, the same cannot be said about SQL tables. A SQL table is bag (i.e., a multiset) of rows, unless constraints are placed on the table to ensure there be no duplicate rows. Thus, to implement a relation as a SQL table, there must be some set of attributes in each relation whose values, taken together, guarantee uniqueness of each row. Any set of attributes that can do this is called a super key (SK). By the definition of a relation as a set of tuples, the set of all attributes must be a super key. If such a set were not a super key, it would allow two or more identical tuples in the relation which would violate the definition of a set. Since super keys are constraints on the data, they must be true for any relation (table) of a relation scheme, thus these super keys are shown in the relation scheme diagram.