Introduction to logical design & creating conceptual tables

Question 1

What do we do In the second database design phase (i.e., logical design)?

Answer 1

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))

Question 2

What is a similarity between OOP and Database design?

Answer 2

§ 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)

Question 3

What does each cell in a table contain?

Answer 3

Each cell in a table contains exactly one value, be it single- or multi-valued

Question 4

Can we mix numerical and textual values in a column?

Answer 4

§ The values of a column/attribute are all from the same domain
§ For example, we cannot mix numerical and textual values in a column

Question 5

Does the order of columns/attributes or rows/values havy any significance?

Answer 5

The order of columns/attributes or rows/values has no significance

Question 6

Is it important that each row in a table is distinct?

Answer 6

it is important that each row is distinct. I.e., we cannot allow a unique
identifier to be common

Question 7

What is a set, in Set theory?

Answer 7

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

Question 8

What is Set theory?

Answer 8

Set theory is a branch of mathematical logic that studies sets

Question 9

What will learning to apply set theory help with?

Answer 9

Learning to apply set theory will help with your understanding of relational
database tables, as well as relational algebra and SQL

Question 10

What is one way of describing a set with a small number of elements?

Answer 10

§ 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}

Question 11

Does it matter in which order the elements of a set occurs?

Answer 11

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

Question 12

What do you do with repeated elements in a set?

Answer 12

Repeated elements are ignored

§ E.g., the set {1,1,2} is the same as {1,2}

Question 13

What can we do to visualize sets and elements?

Answer 13

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)

Question 14

we can indicate that the element x is a member

of the set A by writing:

Answer 14

x € A

Question 15

we can indicate that x is not a member of the set A by writing:

Answer 15

x /€ A (sträcket är över E märket)

Question 16

What would be inconvenient for larger sets?

Answer 16

For larger sets, it would be inconvenient to describe the set by listing its elements

Question 17

What would be an alternative to describing larger sets?

Answer 17

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}

Question 18

In Set operations, what is Union? How does it look like when we Unionize something?

Answer 18

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}

Question 19

In Set operations, what is Intersection? How does it look like when we Intersection something?

Answer 19

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)

Question 20

In Set operations, what is relative complement? How does it look like when we relative complement something?

Answer 20

§ 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}

Question 21

What is disjoint sets?

Answer 21

Sets with no common elements, such as {a,b,c} and {d,e,f}, are known
as disjoint sets

Question 22

In Set operations, what is cartesian product? How does it look like when we cartesian product something?

Answer 22

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)
}

Question 23

In Set Relations, what does Relations (R) describe?

Answer 23

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.

Question 24

In Set Relations, Consider A={Sweden,Finland} and B={Uppsala,Göteborg,Helsinki},
then what is A x B?

Answer 24

{(Sweden,Uppsala),(Sweden,Göteborg),(Sweden,Helsinki),
(Finland,Uppsala),(Finland,Göteborg),(Finland,Helsinki
)}

Question 25

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

Answer 25

The generated R would be:

R = {(Sweden,Uppsala),(Sweden,Göteborg),(Finland,Helsinki)}

Question 26

What is a function?

Answer 26

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

Question 27

In Set Functions, what is the idea behind a function?

Answer 27

The idea behind a function is that we do something to every input
(from A) which generates one output (from B)

Question 28

what does a Set Function from A to B

(denoted f : A → B) signify?

Answer 28

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

Question 29

In Set Functions, what are Functions also called? and why?

Answer 29

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

Question 30

Let A = {a,b,c,d} and B = {1,2,3,4,5}. Create the function f : A → B

Answer 30

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

Question 31

How do you conceptually design tables in the most optimal way, before creating them in
our database during phase 3 (physical design)

Answer 31

To this purpose, we utilize database schemas such as:

Student (StudID, Name, CourseCode)
Course (CourseCode, Name)

Question 32

What constitutes a schema in the logical design phase?

Answer 32

the basic structure of a

table (i.e., its name and its attributes) constitutes the schema

Question 33

What will we use schemas for?

Answer 33

schemas are what we will use to act as a blueprint for our future
relational tables

Question 34

What information can we use to build our schemas in the logical design phase?

Answer 34

§ 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

Question 35

What should you do when you are in the second phase, logical design and you are gonna create your schemas?

Answer 35

For each entity in the ER model, create a schema that includes all the
entity’s simple attributes as well as their unique identifier

Question 36

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?

Answer 36

Student_Course (studentID,courseID,grade)