:care حل شدہ اسائنمنٹس ڈانلوڈ کرنے کے لئے پہلے رجسڑ کریں شکریہ :care

How to Draw Entity Relationship Diagram (ERD) For Vu Final Project Help CS619

How to Create Phase Two Assignment of CS619 Virtual University Of Pakistan Complete Help Including Sample Document.
User avatar
Ali Raza Tawary
❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️
❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️
Posts: 302
Joined: Fri Dec 22, 2017 8:10 am
Location: Kamalia
Contact:
Status: Offline

Achievements

How to Draw Entity Relationship Diagram (ERD) For Vu Final Project Help CS619

Post by Ali Raza Tawary »

Image
Entity
The entity is the basic building block of the E-R Data Model. The term entity 
The name or Label Assigned to items objects that exist in an environment and that have similar properties. It could be a person place event or concept.
An entity-relationship diagram (ERD)is used in a database modeling process. ERD diagram shows a graphical representation of objects or concepts within an information system and their relationships.
 
Entity Attributes

Also known as a column, an attribute is a property or characteristic of the entity that holds it.

An attribute has a name that describes the property and a type that describes the kind of attribute it is, such as varchar for a string, and int for integer. When an ERD is drawn for physical database development, it is important to ensure the use of types that are supported by the target RDBMS.


 
Primary Key

Also known as PK, a primary key is a special kind of entity attribute that uniquely defines a record in a database table. In other words, there must not be two (or more) records that share the same value for the primary key attribute. The ERD example below shows an entity 'Product' with a primary key attribute 'ID', and a preview of table records in database. The third record is invalid because the value of ID 'PDT-0002' is already used by another record.




Foreign Key

Also known as FK, a foreign key is a reference to a primary key in the table. It is used to identify the relationships between entities. Note that foreign keys need not to be unique. Multiple records can share the same values. The ER Diagram example below shows an entity with some columns, among which a foreign key is used in referencing another entity.
 
 
 
 
 
When creating a database, common sense dictates that we use separate tables for different types of entities. Some examples are customers, orders, items, messages, etc... But we also need to have relationships between these tables. For instance, customers make orders, and orders contain items. These relationships need to be represented in the database. Also, when fetching data with SQL, we need to use certain types of JOIN queries to get what we need.
 

Relationship

A relationship between two entities signifies that the two entities are associated with each other somehow. For example, a student might enroll in a course. The entity Student is therefore related to Course, and the relationships are presented as a connector connecting between them.

Carnality

Cardinal defines the possible number of occurrences in one entity which is associated with the number of occurrences in another. For example, ONE team has MANY players. When present in an ERD, the entities Team and Player are inter-connected with a one-to-many relationship. There are several types of database relationships. Today we are going to cover the following:

 
 
 
  1. One to One Relationships
    • One to Many and Many to One Relationships
      • Many to Many Relationships
        • Self Referencing Relationships
 
Image
 
When selecting data from multiple tables with relationships, we will be using the JOIN query. There are several types of JOIN's, and we are going to learn about the the following:
 
  1. Cross Joins
    • Natural Joins
      • Inner Joins
        • Left (Outer) Joins
          • Right (Outer) Joins
 
We will also learn about the ON clause and the USING clause.
 
 
 
One to One Relationships

Let's say you have a table for customers.


Image
We can put the customer address information on a separate table.By Dividing Customers Table in to 2 tables . Customers and Addresses  These Two Tables are Shown Below.

Image

 

Now we have a relationship between the Customers table and the Addresses table. If each address can belong to only one customer, this relationship is "One to One". Keep in mind that this kind of relationship is not very common. Our initial table that included the address along with the customer could have worked fine in most cases.
Notice that now there is a field named "address_id" in the Customers table, that refers to the matching record in the Address table. This is called a "Foreign Key" and it is used for all kinds of database relationships. We will cover this subject later in the article.
 
One To One Relationship In MySQL Using SQL Code.
 
First of All Create Data Base in MySQL Server Via Php Admin.Copy This SQL and Run into MySQL .This Will Creates Table Customers .
 
 
 
 
Create Table addresses  by following sql.
 

Code: Select all



[size=150][font=Times New Roman,Times,serif]CREATE TABLE addresses(
                     address_id INT  PRIMARY KEY NOT NULL,
                       address varchar(80) );[/font][/size]
 

Now Create Table Customers By Using Following Sql.

 

Code: Select all



[size=150][font=Times New Roman,Times,serif]CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(50),
    address_id INT UNIQUE NOT NULL,
 FOREIGN KEY (address_id) REFERENCES addresses(address_id)
);[/font][/size]
 

Result of one to one Relationship in MySQL. 


Image





One to Many and Many to One Relationships


Image
 

This is the most commonly used type of relationship. Consider an e-commerce website, with the following:

Customers can make many orders.
Orders can contain many items.
Items can have descriptions in many languages.
In these cases we would need to create "One to Many" relationships. Here is an example:


Image

Each customer may have zero, one or multiple orders. But an order can belong to only one customer.

Image


How to Implement One to Many Relation Ship Using SQL of Above Example In MySQL.


 Create Customers Table in SQL By Using Following SQL.
 

Code: Select all



[size=150][font=Times New Roman,Times,serif]CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(50));[/font][/size]
 

Create Orders Table By Using Following SQL.

 

Code: Select all



[size=150][font=Times New Roman,Times,serif]CREATE TABLE orders(
 
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date Date,
   order_Amount double NOT NULL,
   FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
 
);[/font][/size]

Result of Above Tables 

Image



Many to Many Relationships
In some cases, you may need multiple instances on both sides of the relationship. For example, each order can contain multiple items. And each item can also be in multiple orders.
For these relationships, we need to create an extra table:

Image


The Items_Orders table has only one purpose, and that is to create a "Many to Many" relationship between the items and the orders.Here is a how we can visualize this kind of relationship.
Image
If you want to include the items_orders records in the graph, it may look like this:
Image

Self Referencing Relationships
This is used when a table needs to have a relationship with itself. For example, let's say you have a referral program. Customers can refer other customers to your shopping website. The table may look like this.

Image

Customers 102 and 103 were referred by the customer 101.
This actually can also be similar to "one to many" relationship since one customer can refer multiple customers. Also it can be visualized like a tree structure:

Image
Foreign Keys
So far we have only learned about some of the concepts. Now it is time to bring them to life using SQL. For this part, we need to understand what Foreign Keys are.

In the relationship examples above, we always had these "****_id" fields that referenced a column in another table. In this example, the customer_id column in the Orders table is a Foreign Key column:


With a database like MySQL, there are two ways to create foreign keys columns:
Image


Defining the Foreign Key Explicitly
Let's create a simple customers table:

Code: Select all


[size=150][font=Times New Roman,Times,serif]CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(100)
);[/font][/size]

Now the orders table, which will contain a Foreign Key:

Code: Select all


[size=150][font=Times New Roman,Times,serif]CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
amount DOUBLE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);[/font][/size]

Both columns (customers.customer_id and orders.customer_id) should be the same exact data structure. If one is INT, the other one should not be BIGINT for example.

Please note that in MySQL only the InnoDB engine has full support for Foreign Keys. But other storage engines will still allow you to specify them without giving any errors. Also the Foreign Key column is indexed automatically, unless you specify another index for it.

Without Explicit Declaration
Same orders table can be created without explicitly declaring the customer_id column to be a Foreign Key

Code: Select all


[size=150][font=Times New Roman,Times,serif]CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
amount DOUBLE,
INDEX (customer_id)
);[/font][/size]

When retrieving data with a JOIN query, you can still treat this column as a Foreign Key even though the database engine is not aware of that relationship.

Code: Select all


[size=150][font=Times New Roman,Times,serif]SELECT * FROM orders
JOIN customers USING(customer_id)[/font][/size]

Visualizing the Relationships
My current favorite software for designing databases and visualizing the Foreign Key relationships is MySQL Workbench.

Image
Once you design your database, you can export the SQL and run it on your server. This comes in very handy for bigger and more complex database designs.
Image
JOIN Queries
To retrieve data from a database that has relationships, we often need to use JOIN queries.

Before we get started, let's create the tables and some sample data to work with.

Code: Select all


[size=150][font=Times New Roman,Times,serif]CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(100)
);

CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
amount DOUBLE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO `customers` (`customer_id`, `customer_name`) VALUES
(1, 'Adam'),
(2, 'Andy'),
(3, 'Joe'),
(4, 'Sandy');

INSERT INTO `orders` (`order_id`, `customer_id`, `amount`) VALUES
(1, 1, 19.99),
(2, 1, 35.15),
(3, 3, 17.56),
(4, 4, 12.34);[/font][/size]

We have 4 customers. One customer has two orders, two customers have one order each, and one customer has no order. Now let's see the different kinds of JOIN queries we can run on these tables.

Cross Join

This is the default type of JOIN query when no condition is specified.
Image
The result is a so called "Cartesian product" of the tables. It means that each row from the first table is matched with each row of the second table. Since each table had 4 rows, we ended up getting a result of 16 rows.

The JOIN keyword can be optionally replaced with a comma instead.
Image
Of course this kind of result is usually not useful. So let's look the other join types.

Natural Join

With this kind of JOIN query, the tables need to have a matching column name. In our case, both the tables have the customer_id column. So, MySQL will join the records only when the value of this column is matching on two records.

Image
As you can see the customer_id column is only displayed once this time, because the database engine treats this as the common column. We can see the two orders placed by Adam, and the other two orders by Joe and Sandy. Finally we are getting some useful information.
Inner Join
When a join condition is specified, an Inner Join is performed. In this case, it would be a good idea to have the customer_id field match on both tables. The results should be similar to the Natural Join.
The results are the same except a small difference. The customer_id column is repeated twice, once for each table. The reason is, we merely asked the database to match the values on these two columns. But it is actually unaware that they represent the same information.

Let's add some more conditions to the query.
Image
This time we received only the orders over $15.
ON Clause
Before moving on to other join types, we need to look at the ON clause. This is useful for putting the JOIN conditions in a separate clause.
Image

Now we can distinguish the JOIN condition from the WHERE clause conditions. But there is also a slight difference in functionality. We will see that in the LEFT JOIN examples.

USING Clause
USING clause is similar to the ON clause, but it's shorter. If a column is the same name on both tables, we can specify it here.
Image
In fact, this is much like the NATURAL JOIN, so the join column (customer_id) is not repeated twice in the results.

Left (Outer) Join
A LEFT JOIN is a type of Outer Join. In these queries, if there is no match found from the second table, the record from the first table is still displayed.
Image

Even though Andy has no orders, his record is still being displayed. The values under the columns of the second table are set to NULL.

This is also useful for finding records that do not have relationships. For example, we can search for customers who have not placed any orders.
Image
All we did was to look for NULL values for the order_id.

Also note that the OUTER keyword is optional. You can just use LEFT JOIN instead of LEFT OUTER JOIN.

Conditionals
Now let's look at a query with a condition.

Image
So what happened to Andy and Sandy? LEFT JOIN was supposed to return customers with no matching orders. The problem is that the WHERE clause is blocking those results. To get them we can try to include the NULL condition as well.

Image
We got Andy but no Sandy. Still this does not look right. To get what we want, we need to use the ON clause.
Image

Now we got everyone, and all orders above $15. As I said earlier, the ON clause sometimes has slightly different functionality than the WHERE clause. In an Outer Join like this one, rows are included even if they do not match the ON clause conditions.



Right (Outer) Join
A RIGHT OUTER JOIN works exactly the same, but the order of the tables are reversed.
Image

This time we have no NULL results because every order has a matching customer record. We can change the order of the tables and get the same results as we did from the LEFT OUTER JOIN.

Image
Now we have those NULL values because the customers table is on the right side of the join.

 


Last bumped by Ali Raza Tawary on Wed May 09, 2018 7:18 am.

Last 10 Members Who Visited This Topic

User avatar Ali Raza Tawary (4)

Return to “Cs619 Final Year Project Phase 2”

Who is online

Users browsing this forum: No registered users and 1 guest