Database Learning Notes

Learning databases isn't actually that hard—it's just that my head feels a bit cold.

Conceptual Logic Transformation Rules — Week 5~8

Key Focus — Relationship

1.*

S-S

The primary key serves as a foreign key.

The primary key of 1 is migrated to n.

With Attributes

If there are attributes, remove the attributes, swap the cardinalities, and then follow the above rules.

Note when modeling the conceptual model: First draw the 1* in the conceptual model.

S-W

A weak entity does not have a primary key and cannot be moved to a strong entity.

1:* Binary S——W
1——>* Primary key——>Primary key + Foreign key

from our teacher

With Attributes

The parentheses indicate that "preference" is a weak entity.

At this point, an error occurs when generating the logical model

Move the attributes of the relationship into the dependent weak entity.

(Here, Relationship_2 should be "state," and the "state" item in "preference" should be the "Date" item.)

Then proceed with the transformation according to the previous rule.

1.1

S-S

If both parties are forced to participate, an alert will be triggered. Depending on the business requirements, one side can be changed to 0-1.

The dominated side will have the foreign key of the dominating side.

To move the foreign key to the right side, the right side should be set to 0-1.

Similarly, for 1-1 and 0-1 relationships, it is equivalent to setting the left side as the dominating side.

For cases where both sides are 0-1, the dominated object is the side with fewer entities.

S-W

A weak entity uses the strong entity as its primary key and foreign key.

Mandatory participation on both sides will trigger an alert. There are two ways to resolve this: either change the dominated side to 0-1, or directly merge the weak entity with the strong entity.

With Attributes

1-1, 0-1:

At this point, a new bidirectional relationship warning may appear. Handle it using the method for cases without attributes.

We merge Manages and Staff. The dominant entity is Branch (diagram omitted), resulting in the physical model:

1-1, 1-1:

Bidirectional association constraints. Modify it to 1-1, 0-1, which is equivalent to the handling method for this scenario.

0-1, 0-1:

Merge the side that is closer to 1-1 (with fewer entities) with the new entity, and treat the original entity as the dominant one.

.

Strong Entity Without Attributes

A : relationship becomes two 1:* relationships.

A new entity is generated—a weak entity.

The primary keys of the original entities on both sides → become part of the new entity: primary key + foreign key (the primary key from the "1" side moves to the "n" side and becomes a foreign key).

Strong Entity with Attributes

Compared to having no attributes, it includes attributes of the association itself, but otherwise there is no significant difference.

Unable to Set Up S-W.

Solutions to resolve the warning:

If there are no attributes, change one of the * to 1.
If there are attributes, merge one relationship with the weak entity to form a 1.* relationship.

Recursion

1.*

The logical model includes the foreign key of the entity itself, which can also be interpreted as moving from one to many.

0-1, 1-* is acceptable. 0-1, 0-* is acceptable.

1-1, 1- will result in a reflexive mandatory warning.*

1.1

Pay attention to setting the dominant role; the remaining rules are the same as in 1.*.

Only 0-1, 0-1 will not trigger a warning.

.

Similar to the binary *.* case, pay attention to renaming.

Various types of *.* will not generate warnings.

Multiple Relationships

Note that the cardinality must be n; participation does not have much effect on it.

If the cardinality is 1, there will be a warning:

Four Cases of Subclass Merging

Let A be the parent class, and B and C be the subclasses.

Enforce Disjointness

There is no need to create A; simply inherit the attributes of A to B and C.

Optional Disjointness

B and C cannot establish a connection with A (preserving the superclass-subclass relationship). Establish a connection between A and D; establish connections between B, C, and D, or connect each to other entities separately.

Force Non-Disjoint

Merge B and C first, then merge the result with A.

Optional Non-Disjoint

Merge B and C, ensuring that B+C cannot establish a connection with A (while preserving the superclass-subclass relationship). Establish a connection between A and D, and connect B+C with D or other entities.

Subclasses

Subclasses inherit the attributes of the parent class. There is no difference among the four types of mandatory/overlapping relationships.

Multivalued Attributes

Create a new entity, a strong entity, separate the multivalued attributes, and establish a 1:* relationship with the original entity.

Normalization — Week 9~10

Taking classes online is so damn "refreshing."

Using this table as an example:

First, make a set of assumptions to derive relationships

Employee ID corresponds to a unique name: ⑥ -> ⑦
Property ID corresponds to a unique address: ① -> ④
An employee can use only one car per day: ②⑥ -> ⑧
A property can only be shown once per day: ①② -> ③⑤⑥
On the same date and time, a car can only be used by one employee: ②③⑧ -> ⑥
On the same date and time, an employee can only view one property: ②③⑥ -> ①⑤

Then Start Combining

We can get ①② -> All

We can get ②③⑧ -> All

We can get ②③⑥ -> All

In summary, we obtain:

Removing Dependencies

The last three lines can derive all information, so these three groups are candidate keys. At the same time, ①② is taken as the primary key.

①② → ④ and ① → ④, so ① → ④ is a partial dependency.

②③⑧ → ⑥ and ②③⑧ → ⑦, so ⑥ → ⑦ is a transitive dependency (also a partial dependency).

②⑥ → ⑧ is a dependency where the determinant is not a key (also a partial dependency).

Exporting the Final Table

① and ④ form one table, ⑥ and ⑦ form another table, ②, ⑥, and ⑧ form a third table, and the remaining ①, ②, ③, ⑤, and ⑥ form a fourth table.

Database Systems and SQL — Weeks 10–13

Create

Creating a Data Type

create type BranchNumber
   from varchar(20)
go

Creating a Default Value

create default D_B001
    as 'B001'
go

Binding Default Values to Columns

execute sp_bindefault D_B001, BranchNumber
go

Create Rule

create rule R_SexType as
      @column in ('F','M')
go

Binding a Rule to a Column

execute sp_bindrule R_SexType, SexType
go

Create Table

create table Staff (
   staffNo              StaffNumber          not null,
   branchNo             BranchNumber         not null,
   Sup_staffNo          varchar(5)           null,
   fNameOfStaff         FirstName            not null,
   lNameOfStaff         LastName             not null,
   Position             varchar(10)          not null,
   sex                  SexType              not null           D_M            R_SexType,
   DOB                  datetime             null,
   salary               money                not null,
   constraint PK_STAFF primary key nonclustered (staffNo)
)# column name          datatype             null/not null      default val    rule  
go

Query

Result Deduplication

select DISTINCT propertyNo
from Viewing

Deduplicating results affects the cardinality, average, and total count, but has no impact on the minimum and maximum values.

Range Query

SELECT *
FROM Staff
WHERE salary BETWEEN 5000 AND 9000
# Values equal to 5000 or 9000 will also be included in the query results

Wildcard Search

select *
from PrivateOwner
where address like '%St%'
# The % symbol functions similarly to the wildcard '*' in regular expressions, used to find strings containing 'St'

Sorting

select *
from PropertyForRent
order by type, rent desc

First, sort by type in alphabetical order, then for the same type, sort rent in descending order.

Grouping

SELECT branchNo,
COUNT(staffNo) AS myCount,
SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
ORDER BY AVG(salary)
# Calculate the number of employees and total income for each branch, sorted by average salary

Removing the GROUP BY branchNo column at this point will cause an error. The following modification should be made:

SELECT 
COUNT(staffNo) AS myCount,
SUM(salary) AS mySum
FROM Staff
# Calculate the total number of employees and total income for the entire company

Correlated Subqueries

select *
from Staff
where salary>(select AVG(salary) from Staff)
# Query for employees whose salary is above the average salary. Cannot be written as salary>AVG(salary)

select *
from Staff S1
where salary>(select AVG(salary) from Staff S2 
where S1.branchNo=S2.branchNo)
# Query for employees in each branch whose salary is above their branch's average salary

Inner, Left, Right, and Full Joins

SELECT *
FROM Staff, Branch
WHERE Staff.branchNo = Branch.branchNo
AND street = '163 Main St'
# The data from both tables are merged into a single result, joined by matching branch information and a specific street.

SELECT *
FROM Branch b LEFT JOIN PropertyForRent p
ON p.city = b.city
# Retains all data from the left table; unmatched entries on the right are filled with null.

SELECT *
FROM Branch b RIGHT JOIN PropertyForRent p
ON p.city = b.city
# Retains all data from the right table; unmatched entries on the left are filled with null.

SELECT *
FROM Branch b FULL JOIN PropertyForRent p
ON p.city = b.city
# Retains all data from both tables; unmatched entries on either side are filled with null.

Cross-Table Queries

select *
from Registers
where staffNo in
	(select staffNo
	 from Staff
	 where branchNo = 
		(select branchNo
		 from Branch
		 where street = '163 Main St'))
# Query registration information for all staff members whose branch is located on this street

select distinct clientNo
from Viewing v1
where NOT EXISTS
    (select *
 	from PropertyForRent P
 	where rooms=3 and NOT EXISTS
 		(select * 
 		from Viewing v2
		where v2.clientNo=v1.clientNo and v2.propertyNo=p.propertyNo))
# Whoa (Find clients who have viewed all 3-bedroom properties — meaning there is no 3-bedroom property they haven't seen)

select distinct clientNo
from Viewing v1
where EXISTS
    (select *
 	from PropertyForRent P
 	where rooms=3 and EXISTS
 		(select * 
 		from Viewing v2
		where v2.clientNo=v1.clientNo and v2.propertyNo=p.propertyNo))
# Find clients who have viewed any 3-bedroom property

select distinct clientNo
from Viewing v1
where propertyNo IN
    (select propertyNo
 	from PropertyForRent
 	where rooms=3)
# Equivalent query using the IN clause

Set Operations

(select * from Branch)
 union
(select * from PropertyForRent)
# Union

(select * from Branch)
 intersect
(select * from PropertyForRent)
# Intersection

(select * from Branch)
 except
(select * from PropertyForRent)
# Difference

select distinct clientNo
from Viewing v1
where NOT EXISTS
    ((select propertyNo
 	from PropertyForRent P
 	where rooms=3)
 	except
 	(select propertyNo
 	from Viewing v2
	where v2.clientNo=v1.clientNo))
# Alternative method to find clients who have viewed all 3-bedroom properties

some/any/all

select *
from Staff
where salary > any
	(select salary
	 from Staff
	 where branchNo = 'B003')
# Using some/any here produces the same effect, and select salary has the same effect as select MIN(salary)

select *
from Staff
where salary > all
	(select max(salary)
	 from Staff
	 where branchNo = 'B003')
# Here, select salary has the same effect as select MAX(salary)

Views - Week 13

Create View

CREATE VIEW StaffPropCnt(branchNo, staffNo, cnt)
AS 
SELECT s.branchNo, s.staffNo, COUNT(*)
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
GROUP BY s.branchNo, s.staffNo

Querying Views

# Views can be treated as tables for further querying
SELECT staffNo, cnt
FROM StaffPropCnt
WHERE branchNo = 'B003'
ORDER BY staffNo

# Equivalent to
SELECT s.staffNo AS staffNo, COUNT(*) AS cnt
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo AND s.branchNo = 'B003'
GROUP BY s.branchNo, s.staffNo

View Modification

insert into StaffPropCnt
values('B003','SG5',2)
# Error: Cannot modify the view as it would affect the underlying table (insufficient information).

创建角色

CREATE ROLE Manager

Granting Permissions

grant select, insert, update, delete, references, alter
on Staff
to Manager
with grant option

Revoke Permissions

revoke references 
on Staff
from Manager 
cascade

Transaction Concurrency—Week 14

Serialization

Conflicts may arise from read-write and write-write operations on the same transaction across different threads.

By treating each thread as a node and adding directed edges for read-write and write-write operations on the same transaction across different threads, if the generated graph is acyclic, it indicates that the parallel schedule is conflict serializable.

~~Scheduling for views belongs to the NP-complete problem and is unsolvable.~~

Locking

Read Lock / Shared Lock / read_lock: Allows reading by self and others, but prohibits writing by self and others.

Write Lock / Exclusive Lock / write_lock: Allows reading and writing by self, but prohibits reading and writing by others.

If someone else holds a shared lock, you can only acquire a shared lock; if someone else holds an exclusive lock, you cannot acquire any lock.

Two-phase locking can prevent issues such as lost updates, uncommitted dependencies, and inconsistent analysis.

Disadvantages include:

Cascading rollbacks — Solutions include releasing locks at the end of a transaction or releasing exclusive locks at the end of a transaction.
Deadlocks — Solutions include lock timeout, wait-die & wound-wait, and timestamp ordering.

Thomas Write Rule:

A writes first, then B reads; if A rolls back, the timestamp moves after B.

A writes first, then B writes; A's write is discarded.

Ensure all locks are acquired before releasing any lock.

Optimistic Concurrency Control

Performing conflict checks only at the end of a transaction can improve concurrency.

Data Granularity

Finer granularity allows for higher concurrency but requires more locking information, and vice versa.

Intention Locks?

Not concerned.

Security — Week 16

This one depends on yourself.

Risk Causes

Hardware failure
Failure of DBMS security mechanisms
Unauthorized modifications or exceeding authorized scope of database operations
Inadequate security policies or procedures by database administrators
Programmers creating "backdoors," insufficient program change controls, or lack of employee security training
Unauthorized data access through identity theft, hacker intrusions for extortion, virus infections, etc.

Database Protection Measures

Authorization and Access Control
Views
Backup and Recovery
Integrity
Encryption (Symmetric, Asymmetric)
RAID Technology

DBMS and Network Security Protection Measures

Proxy Server
Firewall
Kerberos
Message Digest Algorithm and Digital Signature
Digital Certificate
Secure Electronic Transaction and Secure Transaction Technology
Secure Sockets Layer and Secure HTTP
Java Security
ActiveX Security