Introduction to logical design & creating conceptual tables Flashcards
What do we do In the second database design phase (i.e., logical design)?
In the second database design phase (i.e., logical design) we translate
the ER model to a set of conceptual relational tables called schemas
(In the logical database design phase, our focus turns to conceptually
designing these tables in the most optimal way, before creating them in
our database during phase 3 (physical design))
What is a similarity between OOP and Database design?
§ Each table has a distinct name. E.g., we cannot have two tables called “Student”,
much like we cannot have two classes named “Student” in OOP (at least not in the
same namespace)
What does each cell in a table contain?
Each cell in a table contains exactly one value, be it single- or multi-valued
Can we mix numerical and textual values in a column?
§ The values of a column/attribute are all from the same domain
§ For example, we cannot mix numerical and textual values in a column
Does the order of columns/attributes or rows/values havy any significance?
The order of columns/attributes or rows/values has no significance
Is it important that each row in a table is distinct?
it is important that each row is distinct. I.e., we cannot allow a unique
identifier to be common
What is a set, in Set theory?
A set is any collection of objects, which are referred to as
elements/members of a set
§ For example, the set of all the registered students taking this course
What is Set theory?
Set theory is a branch of mathematical logic that studies sets
What will learning to apply set theory help with?
Learning to apply set theory will help with your understanding of relational
database tables, as well as relational algebra and SQL
What is one way of describing a set with a small number of elements?
§ One way of describing a set with a small number of elements is by listing
them. E.g., the set of all positive integers that are less than 4 can be
written as: {1, 2, 3}
Does it matter in which order the elements of a set occurs?
The order in which the elements of a set occurs does not matter
§ Much like how the columns in a spreadsheet table does not matter
What do you do with repeated elements in a set?
Repeated elements are ignored
§ E.g., the set {1,1,2} is the same as {1,2}
What can we do to visualize sets and elements?
To visualize sets, we can use uppercase letters (e.g., A,B,C) and to
visualize elements of sets, we can use lowercase letters (e.g., a,b,c)
we can indicate that the element x is a member
of the set A by writing:
x € A
we can indicate that x is not a member of the set A by writing:
x /€ A (sträcket är över E märket)
What would be inconvenient for larger sets?
For larger sets, it would be inconvenient to describe the set by listing its elements
What would be an alternative to describing larger sets?
an alternative would be to specify a property that all the elements
have in common
§ For this purpose, we can use P(x) to describe a proposition, P, concerning the
variable x. A set defined by this proposition would look like:
{x|P(x)}
- Where “|” means “such that”
Example:
{x|x is a positive integer less than 4}
In Set operations, what is Union? How does it look like when we Unionize something?
Say A and B are two sets, we can unionize these by writing A ⋃ B:
A ⋃ B = {x|x ∈ A or x ∈ B}
§ This means that we will take all the elements from A and combine them with all the elements from B while eliminating duplicates:
A = {1,2,2,4}
B = {1,13,30}
A ⋃ B = {1,2,4,13,30}
In Set operations, what is Intersection? How does it look like when we Intersection something?
If A and B are sets, we define their intersection as the set consisting of
all elements that belong to A and B and write it as A ⋂ B:
A ⋂ B = {x|x ∈ A and x ∈ B}
This means that we will take all the elements that belong to A and B,
while removing duplicates:
A = {1,2,2,4}
B = {1,13,30}
A ⋂ B = {1}
(Both A and B has the number 1, therefore the intersection shows the number 1)
In Set operations, what is relative complement? How does it look like when we relative complement something?
§ We can use relative complement to get the difference between two sets,
based on the first set:
A\B = {x|x ∈ A and x ∉ B}
§ I.e., we can retrieve all elements from set A which do not occur in set B:
A = {a,b,c}
B = {d,e,f,a}
A\B = {b,c}
What is disjoint sets?
Sets with no common elements, such as {a,b,c} and {d,e,f}, are known
as disjoint sets
In Set operations, what is cartesian product? How does it look like when we cartesian product something?
The cartesian product operation allows us to retrieve a set of all the
ordered pairs from two sets, A and B
* An ordered pair (x,y) is a collection of
2 elements
If A and B are two nonempty sets, we define the Cartesian Product as:
A x B = {(x,y)|x ∈ A and y ∈ B}
Example:
If we have:
Children = {Carl, Mary, Sophie}
Parents = {Charles, Sara}
Then:
Children x Parents = {
(Carl,Charles),(Carl,Sara),
(Mary,Charles),(Mary,Sara),
(Sophie,Charles),(Sophie,Sara)
}
In Set Relations, what does Relations (R) describe?
Relations describe associations between objects. a relation, R, between the two
nonempty sets A and B is a set of pairs:
R = {(a1,b1),(a2,b2),…}
§ where every a(index) is a member of A and every b(index) is a member of B. In other words, R, acts as a subset of
A x B.
In Set Relations, Consider A={Sweden,Finland} and B={Uppsala,Göteborg,Helsinki},
then what is A x B?
{(Sweden,Uppsala),(Sweden,Göteborg),(Sweden,Helsinki),
(Finland,Uppsala),(Finland,Göteborg),(Finland,Helsinki
)}
In Set Relations, Consider A={Sweden,Finland} and B={Uppsala,Göteborg,Helsinki},
What is the generated R in this relationship?:
a R b iff b is a city in a
The generated R would be:
R = {(Sweden,Uppsala),(Sweden,Göteborg),(Finland,Helsinki)}
What is a function?
A function is something we’re all familiar with from mathematics. E.g.,
the function f(x)=x2. For every input, x, we get an output, x2. Such as:
f(2)=4, f(3)=9 etc
In Set Functions, what is the idea behind a function?
The idea behind a function is that we do something to every input
(from A) which generates one output (from B)
what does a Set Function from A to B
(denoted f : A → B) signify?
A function from A to B (denoted f : A → B) signifies a relation from A to B
such that for all elements x ∈ Dom(f), f(x) contains just one element of B
• Where Dom represents the domain, i.e., the input must come from our domain
or set A
• The generated output must instead be something within our
range/codomain, i.e., an element from our set B
In Set Functions, what are Functions also called? and why?
Functions are also called mappings or transformations, since they can
be viewed as rules. One such rule being that for every element x ∈ A,
there is an element y ∈ B such that (x,y) ∈ F
§ In other words, for every input x, there is some output y
Let A = {a,b,c,d} and B = {1,2,3,4,5}. Create the function f : A → B
Let A = {a,b,c,d} and B = {1,2,3,4,5} and we create the following
function f : A → B as follows:
f = {(a,3),(b,2),(c,4),(d,1)}
§ Note that the element 5 appears in the B-set but is not mapped to
another value. This is still in accordance with the rules of our function.
I.e., we don’t necessarily have to map our inputs to all outputs for it to
be a valid function
How do you conceptually design tables in the most optimal way, before creating them in
our database during phase 3 (physical design)
To this purpose, we utilize database schemas such as:
Student (StudID, Name, CourseCode)
Course (CourseCode, Name)
What constitutes a schema in the logical design phase?
the basic structure of a
table (i.e., its name and its attributes) constitutes the schema
What will we use schemas for?
schemas are what we will use to act as a blueprint for our future
relational tables
What information can we use to build our schemas in the logical design phase?
§ From the ER models created during phase 1 (Conceptual Design), we
know of the attributes and relationships that make out our data models
§ We can use this information to build our schemas
What should you do when you are in the second phase, logical design and you are gonna create your schemas?
For each entity in the ER model, create a schema that includes all the
entity’s simple attributes as well as their unique identifier
If we have:
Student (studentID,firstName,lastName,email)
Course (courseID,courseName,credits,pace)
How could the schema Student_Course look like representing the two entities relationship?
Student_Course (studentID,courseID,grade)
