Entity Relationship Diagram? (2 Viewers)

[oompaloompaman]

Hey Guys!

i am doing an assignment on Transaction Processing Systems atm and i've finished my database on a video store...however for the theory part...i'm suppose to draw an entity relationship diagram and i have no idea what it is!...Could anyone please help and explain it for me?

 

[Wilmo]

Etity Relationship Diagram (ERD) = Schematic diagram (Schema)

Basically it is a table showing the links between the primary and foreign keys in your databases.

Table 1
_________
|PrimaryKey|
|_Random_ |
|_Random_ |
|Foreign Key|

Table 2
_________
|PrimaryKey|
|_Random_ |
|_Random_ |
|Foreign Key|


The primary key in table one connects to the foreign key in table 2 (one to one, one to many or many to many)... If you are using microsoft acess its really easy because it does it for you.

 

[Freedom_Dragon]

Entity Relationship Diagram/ERD.

An ERD is a graphical method of identifying the entities within the database and showing the relationship(s) between these entites.
An entity can be seen graphically as a table eg: Customer Details.

An entity consist of abtributes/fields.
Eg: Cust ID, First Name, Surname etc...

There are 3 different types of relationships that can be used when drawing up an ERD.

1) 1-To-1 relationship: Occurs when only 1 record in the first entity is related to exactly 1 record in the second entity

2) 1-To-Many relationship: Occurs when only 1 record in the first entity is related to many other records in many other entites.

3) Many-To-Many relationship: Occurs when more than 1 record in the first entity is related to more than 1 record in many other entities and the records in those entities are furthur related to other records in many other entites. Causing a chain of Many-To-Many.

If u have finished ur DB assessment, then i'll assume that uve created a schema.
So basically the schema u created in ur DB is an ERD.
This is not the best explaination...but hopfully its not too confusing. The only way to fully understand this is to refer to examples of ERDs from text books/web

For more reference visit
http://www.boredofstudies.org/community/showthread.php?p=637931#post637931

 

[redslert]

wilmo and Freedom_Dragon summed it up nicely

here's a pictorial example
this came from Microsoft Access, I was using a sonic theme hehe

 

[oompaloompaman]

oh cool thanks guys...

so basically...i've made my system in access...and i just have check out the relationships....The entity is just the relationship between all the tables yeh?

 

[Wilmo]

MS Access should do it for you... I have a mac so i cant tell you what your supposed to do, but you just need to choose view then relationships if i remember correctly. Then drag your primary key into your other entity, double click on the line showing relationship, and then do something really simple that i cant remember.

Thats really not helpful, so someone else would be good to explain it to you.

 

[SamD]

The relationships screen in Access is the schema or ERD.

The entities are the tables, the attributes are the fields, and the relationships are represented as the lines between primary and secondary keys in the tables (usually 1 to many).

HTH
Sam

 

[craz]

Using access is great for looking at relationships and ERDs!

 

[wakarimashita]

i dont know if this will complicate or answer some questions for you but here goes my explanation



- an ERD is not the same as a schema

- an ERD is used in the design process, a schema is used in the implementation process.

- ERD you can have multivalued attributes eg you can have an attribute called phone number (this is multivalued because people can have a number of difference phone numbers - home, work, mobile)
Whereas in the schema this cannot happen, you must decide a way to implement the situation - some options include
1. having an attribute for each option (this may cause alot of NULL values)
2. you can have a 'type of number' attribute, which uses a coding system (eg 1=home, 2=work...) then have a phone number attribute where you actually store the phone number.

- the ERD is not normalised (you can have composite attributes eg address), whereas in the schema it should be normalised to the 2nd or 3rd normalisation (eg address would be seperated into a number of attributes - street no, street, suburb, city, postcode)



Redslert's diagram, was a schema. This is an example of a ERD (using peter chen's notation, i think it's one that is most commonly used).

hope i havent confused you...

 

[rckl]

the diagram above looks like a system flow chart..

i think the ERD from MS Access explains it better

 

[SamD]

There are numerous techniques for drawing ERDs and schemas. Peter Chens is one of the older techniques (maybe it was the original technique, I forget), however to my knowledge it is seldom used anymore (some older texts would refer to it). Much of my own work involves developing DB applications based on SQL server and/or JET (MS-Access). I am unaware of any current database developers who use Chen's technique. Regardless, Chen's ERD technique still models exactly the same information as an "Access Relationship's Screen" style schema. Indeed both ERDs and schemas model the same thing.

I accept Wakar's point that the term ERD is generally used during design, whereas the term 'schema' is more often used in relation to an operational database. However, the operational schema (created from the final ERD) is not necessarily normalised. There are countless commercial relational databases that have not been completely (or correctly) normalised to 3NF, but obviously they still have a schema.

In my view, the distinction between ERDs and schemas is insignificant. Particularly in regard to the IPT course.

HTH
Sam

 

[wakarimashita]

Notations for ERD:
There are many ERD notations, but the notations that are commonly used now, are derieved from Peter Chen's notation (aspects from Chen's notation that is not used includes the composite entity and optionalities).

ERD and Schema
The Schema 'may' represent everything that an ERD does, but that does not mean an ERD is a Schema or they can be used interchangably.

An ERD is a conceptual model, it is used to represent the relationships between data (that is required to be stored in the DB). When you map the ERD into a Schema, a decision is made, that is to implement the database using the 'Relational Model' (a conceptual model). Because an ERD may be implemented using the 'Object-oriented model' where in that case you would not use a Schema to represent your model, you would use a 'UML diagram'.

I know this ideas of object-oriented DB model (or others) are not required in the IPT course, which is probably why Sam is saying that the distinction between ERDs and schemas in insignificant in the IPT course, however I think that it is important for people studying IPT to understand:
* the process of developing a Database. From an ERD (DB design) to Schema (DB implementation). Because there are significant decisions that needs to be made between the two stages (althought the IPT course only covers the relational model i think people need to know that other models do exist - as the relational model is not always the best model to use).
* The schema (from MS Access) is only created when the DB is completed, so if you're going to use the schema in the design process - you will print out the schema from Access and place it into the design section of your documentation, which does not make much sense, since the schema is only available after you've implemented your DB.

Normalisation
It's true that not all DB are normalised to 3NF (or BCNF) when DBs are implemented, this is because:
1. the DB is not designed properly (which does happen to many commercial businesses).
2. to improve performance. (This may seem a bit odd at first, but read on )
When queries are performed over two relations, these relations need to be JOINed before they can perform the query, and JOINing two relations is a costly operation. So if there are queries which are performed on a regular basis (eg the DB could automatically execute a query every hour) then storing these two relations as one will improve the performance significantly. This technique is known as 'Controlled Redundancy'.

So... not all DB needs to be normalised to the 3NF, however if they are supposed to normalise to the 3NF, this will be represented in the schema and not in the ERD. Normalisation does not occur before the DB design stage, it happens before the DB implementation stage. (the stages that are involved in the DB development process is also, i think, important in the IPT course. Because you learn the System Development Cycle in year 11 and the DB Development Cycle just extends from that, ie the stages are generally the same - you get the data (this also includes business requirements), you design the DB by locating the relationships between the data, you implement the DB etc...)

Cheers!
Waka