Hijak Upz79

Content type

User Generated

Subject

Computer Science

Type

Homework

Uploaded By

qbhtc758

Pages

Rating

SQL Database

To design a good SQL Database, we should solve these questions.

1. An entity-relationship model is a graphical representation of entities but what entities can we

think of for a medium size company?

We should think of entities that relate to one another - Relationship is the key. A relation

between two entities (or more) that will show the type of relationship and tell whether it's one-

to-many(1-M) or many-to-many(M-N) or one-to-one(1-1).

For example, if an employee must work for the certain department, this can be sketched by

double lines called ''total or mandatory participation". And if an employee may or may not

manage a department, this can be sketched by a single line called "partial or optional

participation".

The relationship can have attributes.

For example, an employee may act as a supervisor for one or more employees, while the

employee may be supervised by an employee (one to many) also called Recursive Relationship.

2. What's a weak entity?

A weak entity is simply an entity where it's existence depends on another entity.

Example: (son, dougher, ..etc) with the absence of the employee table. It's sketched same as

normal but with double lines.

3. What's Partial Key?

A weak entity has a "partial key". It's one or more attributes that uniquely identify a weak entity

for a given owner entity.

4. What is a Weak Entity Relationship?

Whenever there is a relationship between a weak entity and another entity, it's sketched same

with double lines.

5. What are attributes, primary key, composite attribute?

Attribute simple represents a piece of data in the table, like an address, salary, and date.

The primary key is sketched the same as a normal attribute but with underline.

Composite Attribute is a value that composed of some other values, for example, you may have

a name that's composed of (first name, middle name, and last name).

6. What is Multi-valued Attribute and Derived Attribute?

Multiple values for a single column, for example, the locations of a department may have more

than one value at the same time (locations).

Derive Attribute is when you have a column where it's valued could be calculated from another

column, for example we can know the number of employees works on a specific department by

counting the number of rows.

7. Logical design? what is that?

Mapping the regular entity types(primary key), weak entity types(foreign keys and partial keys),

Relationship types 1:1, 1:M, M:N

As an example can be SSN to Employee SNN or Name to Project Name, etc...

8. What is the meaning of Relational Integrity Constraints?

Unique key constraints, entity Integrity constraints, referential integrity constraint; those key

can't be duplicated. Deleting rows from M-side or deleting rows from the linking tables in many

to many doesn't violate the referential integrity constraint.

9. What is normalization

Is a process where you take your database design and apply a set of formal criteria of rules.

There is normal, second and third normal form.

for example, if you have a customer and this customer can have two emails, the main email and

the alternative one... you can just put two columns in your table each column represent one of

these emails (repeating the group).

second: let's say you have a table that stores books information; a book could have a name,

author, and date released... we can have a book that's written by more than one author but we

can't have more than one book with the same book name and the same author. So, both name

and author form a composite primary key.

Third: concerned with the non-key attribute that relies on another non-key attribute and not the

primary key.

Denormalization: break the normalization rules for performance.

10. SQL? and what are some examples of the use of it?

The Structured Query Language (SQL)

Unformatted Attachment Preview

SQL Database To design a good SQL Database, we should solve these questions. 1. An entity-relationship model is a graphical representation of entities but what entities can we think of for a medium size company? We should think of entities that relate to one another - Relationship is the key. A relation between two entities (or more) that will show the type of relationship and tell whether it's one to-many(1-M) or many-to-many(M-N) or one-to-one(1-1). For example, if an employee must work for the certain department, this can be sketched by double lines called ''total or mandatory participation". And if an employee may or may not manage a department, this can be sketched by a single line called "partial or optional participation". The relationship can have attributes. For example, an employee may act as a supervisor for one or more employees, while the employee may be supervised by an em ...
Purchase document to see full attachment

Tags: analyzing Visualizing