ERD

Question 1

Entity Symbol

Answer 1

Question 2

Attribute Symbol

Answer 2

Question 3

Relationship Symbol

Answer 3

Question 4

Entity

Answer 4

An entity can be a person, place, event, or object that is relevant to a given system. For example, a school system may include students, teachers, major courses, subjects, fees, and other items. Entities are represented in ER diagrams by a rectangle and named using singular nouns.

Question 5

Weak Entity

Answer 5

A weak entity is an entity that depends on the existence of another entity. In more technical terms it can defined as an entity that cannot be identified by its own attributes. It uses a foreign key combined with its attributed to form the primary key. An entity like order item is a good example for this. The order item will be meaningless without an order so it depends on the existence of order.

Question 6

Attribute

Answer 6

An attribute is a property, trait, or characteristic of an entity, relationship, or another attribute. For example, the attribute Inventory Item Name is an attribute of the entity Inventory Item. An entity can have as many attributes as necessary.

Meanwhile, attributes can also have their own specific attributes. For example, the attribute “customer address” can have the attributes number, street, city, and state. These are called composite attributes. Note that some top level ER diagrams do not show attributes for the sake of simplicity. In those that do, however, attributes are represented by oval shapes.

Question 7

Multivalued Attribute

Answer 7

If an attribute can have more than one value it is called an multivalued attribute. It is important to note that this is different to an attribute having its own attributes. For example a teacher entity can have multiple subject values.

Question 8

Derived Attribute

Answer 8

An attribute based on another attribute. This is found rarely in ER diagrams. For example for a circle the area can be derived from the radius.

Question 9

Relationship

Answer 9

A relationship describes how entities interact. For example, the entity “carpenter” may be related to the entity “table” by the relationship “builds” or “makes”. Relationships are represented by diamond shapes and are labeled using verbs.

Question 10

Recursive Relationship

Answer 10

If the same entity participates more than once in a relationship it is known as a recursive relationship. In the below example an employee can be a supervisor and be supervised, so there is a recursive relationship.

Question 11

Cardinality and Ordinality

Answer 11

These two further defines relationships between entities by placing the relationship in the context of numbers. In an email system, for example, one account can have multiple contacts. The relationship in this case follows a “one to many” model. There are number of notations used to present cardinality in ER diagrams. Chen, UML, Crow’s foot, Bachman are some of the popular notations. Creately supports Chen, UML and Crow’s foot notations.The following example uses UML to show cardinality.

Answer 12

A Customer has a one-to-many relationship with a Purchase Order because a customer can place many orders, but a given purchase order can be placed by only one customer. The relationship is optional because zero customers might place a given order (it might be placed by someone not previously defined as a customer).

A Purchase Order has a many-to-many relationship with a Stock Item because a purchase order can refer to many stock items, and a stock item can be referred to by many purchase orders. However, you do not know which purchase orders refer to which stock items.

Therefore, you introduce the notion of a Line Item. A Purchase Order has a one-to-many relationship with a Line Item because a purchase order can list many line items, but a given line item can be listed by only one purchase order.

A LineItem has a many-to-one relationship with a StockItem because a line item can refer to only one stock item, but a given stock item can be referred to by many line items. The relationship is optional because zero line items might refer to a given stock item.

Question 13

Answer 13

One to One

One A is associated with one B.

Question 14

Answer 14

One to Many

One A is associated with one or more Bs.

Question 15

Answer 15

Many to Many

One or more As are associatd with one or more Bs.

Question 16

Answer 16

One to Zero or One

On A is associated with Zero or one B.

Question 17

Answer 17

One to Zero or Many

One A is associated with Zero or many Bs.

Because of the one circle, this means that the relationship is parly optional.

Question 18

Answer 18

One to One and One

One A is associated with one B and one C.

Question 19

Answer 19

One to One or One

One A is associated with one B or one C (but not both).

Answer 20

When designing a datbase determine the levels assocaited with the correct number of tables and broken down tables, and FKs and Pks.

Question 21

Create Table Syntax

Answer 21

CREATE TABLE Customers

CREATE [COMMAND] TABLE [OBJECT] Customers [Name of Object} (…) [Lines in Table]

Question 23

Create a Table “Customers” with Cust No, Cust Name, Street, City, State, Zip, Phone, PK CustNo

Answer 23

CREATE TABLE Customers ( CustNo NUMBER(3) NOT NULL, CustName VARCHAR2(30) NOT NULL, Street VARCHAR2(20) NOT NULL, City VARCHAR2(20) NOT NULL, State CHAR(2) NOT NULL, Zip VARCHAR2(10) NOT NULL, Phone VARCHAR2(12), PRIMARY KEY (CustNo) );