DBS quiz 1

DBS- a computerized record keeping system

The database, the software, hardware, and its users

• Facts about an entity or an event. (Raw data that needs to be processed in order to become useful)
• Individual test scores

• Processed data
• Average test score of a class

Anything you type into a database

Response to a query

Operational data and decision support data (DSD)

• Data that cannot be 100% removed from the database
• (Can’t delete 100% of students or their IDs, their grades, etc. only if they move schools)

• Subset of persistent data.
• Data subject to operations.
• Core data and relationships about core entities

Core data and associate data

• Subset of operational data.
• Data about the core entities of a database (what the database is focused on)
• For example: students, banking customers

• Subset of operational data.
• Data about relationships among core entities.
• For example: if name (core data) and age (core data) are represented in a column next to each other- then they are associate data

Unary, binary, ternary

• Object is related to itself
• ie. Parts made of sub-parts

• Two involved in relationship.
• ie. Part is produced by a producer

• 3 parts.
• Part, producer, seller

• DSD- subset of persistent data.
• Data used to perform operations, not physically stored.
• ie. Total number of students in class

A collection of persistent data regarding an enterprise.

Database, software, hardware, users

Database management system (DBMS)

Intercepting query, checks validity of query (in terms of syntax and semantics), and if ok- it implements and resolves to entity the issued query.

• Incomplete languages with only minimum # of statements needed to issue queries (no do while like java).
• DBMS must always be able to handle a data language (like SQL).

Need to have hardware to run software

• People who use DBS....
• End users, application programmer, database administrator

Customers, people who know nothing about database systems (yet have all authority)

Knows at least one data language (can use queries, create reports, etc.)

• Person or team in charge of collecting needs of the user, and designing, creating, populating, and protecting the DB.
• If the organization is big (Amazon/Walmart), designing responsibilities go to Data Manager.

• Designs DB
• Decides content of DB
• Ensures satisfaction of the user and their requests
• Provides security
• Creates data dictionary

Data about data. This is your job security, don’t make it easy for someone else to take over.

• Integrity
• Inconsistency
• Redundancy
• Security
• Balancing of conflicts
• Data independence

• Correctness of data...
• If you say nobody makes more than 100 grand but John is listed having 125 grand
• Make sure there is a threshold/restriction for data entries?

• Data is correct but not matching...
• ie . Two different salaries under a name (after raise) OR two different last names (after a divorce)
• Make sure ALL data is updated after changes

• Having more than one copy of data...
• Uncontrolled redundancy- Storing the same information unnecessarily (like a customers address who buys several things)

• Getting minimum redundancy for sake of navigating among tables.
• ie. Backing up a companies files nightly

• Internal- security against people who have access to database (housing dept. doesn’t need to see your grades, lvl 5 SCP clearance)
• External- security against people who are outside your organization.

• ie. One department wants day/month/year, other wants month/day/year.
• OR one wants all metric units, other wants empirical (caused NASA explosion)

Not sure yet

A collection of data that have the same type

Subset of a domain

A subset of Cartesian product of not necessarily distinct domains

(A row in a table) An instance of Cartesian product of not necessarily distinct domains that appear in a relation

• Every value in a given attribute is atomic
• All attribute values of a given attribute come from same domain
• Order of tuple and attributes is insignificant
• No duplicate tuples

Number of tuples

Number of attributes

Attribute or group of attributes whose values can UNIQUELY identify every tuple in a relation

Uniqueness and minimality

The selected candidate key

Candidate key that’s not your primary key

Candidate key that satisfies ONLY uniqueness (ST-ID, Name, Age)

A primary key or any component of it must have a value (can’t be null) and cannot be known

Referring to a tuple that doesn’t exist ( student listed one class but not database)

Location you’re extracting primary key from

An attribute (or group) that appears in multiple relations (and comes from same domain) but is not used as the primary key

• The degree (# of attributes) of both relations must be the same
• Data for corresponding attributes (A and A’) must be from same domain
• No duplicates

• Must be union compatible
• Common elements/ tuples is result

Result is unique element in first set. S1={1,2,3}, S2={1,4,3}, S1-S2=2... S2-S1=4

Takes horizontal subset of relation where __ = __ and places that tuple into relation __

Concatenation every tuple in one relation with every tuple in another

(W1 = R1 equi join R2 where R1.B = R2.E) Concatenation every tuple in one relation with every tuple in the other. Result is many attributes

Concatenation all tuples with each other and only keep ONE copy of attribute values

Equi join where there are duplicates under a condition

• Equi join
• Natural join
• Theta join

• Left outer join
• Right outer join
• Full outer join

• (X1 = R1 left outer join R2 where __ = __;)
• Normally join where there are duplicates then add tuples from the left relation that didn’t have a match

• (X1 = R1 right outer join R2 where __ = __;)
• Normally join where there are duplicates then add tuples from the right relation that didn’t have a match

• (X1 = R1 left outer join R2 where __ = __;)
• Normally join where there are duplicates then add tuples from the left relation and then the right one that didn’t have a match

• (X1 = R1 semi join R2 where __ = __;)
• Normally join and then drop all R2 attributes and duplicates

• N = degree of dividee, K = degree of dividend.
• K must be > n
• K = m+n
• N must come from same domain

• Find n (degree of divisor)
• Find k in dividend (remember k=m+n)
• Makes sure n is from same domain
• Get each corresponding value for values in divisor and place in their own set
• Intersect sets
• New set equals the result of intersection