ERD andSQL ITECH 7401 LEADERSHIP IN

Introduction

Contents

1. Introduction. 3
2. ER Diagram.. 4
3. SQL statements. 4

3.1 Create Table queries. 5

3.2 Insert data queries. 6

4. Result and Conclusion. 9

References. 11

1. Introduction

Toys 4 U is mainly an organisation specialised in toys that essentially employ families, kindergarten and play groups which are actually very large, or as they think commonly. They also sell kindergarten furniture, particularly for infants, which they usually felt would really be of great significance. Toys 4 U will probably own a number of copies (TOYCOPY) of each toy (TOY) or, for all purposes, conceive about them, which is very important practically. For example, for the most part, a shop will be a 10-copy of Rocking Horse ‘s product, which reveals how it still sells child and baby equipment for really hiring in a subtle way. For the most part, Rocking Horse was one TOY in the archive, and each copy was basically a TOYCOPY in general, since they were thinking especially well. A hire transaction (HIRE) includes one or two copies (MEMBERSHIP) that demonstrate how a hire transaction (HIRE) includes a lot two copies (MEMBERSHIP), which is certainly important, or for all purposes, being kept in an entity (MEMBERSHIP), showing that toy 4 U may have very many copies in particular. A copy can be certainly leased many times over its lifespan for every reason; thus, most of the time there is a M: N partnership between HIRE and TOYCOPY, a kind of additional showing how toies 4 U undoubtedly have very many copies (TOYCOPY) of each toy (TOY), in every manner contrary to popula for any reason. DETAILHIRE is certainly the bridge table for this partnership to be solved, specifically to show how Toys 4 U generally is a business that specialises in toying, mostly for party playgrounds, kindergartens and groups in particular, and that shows how DETAILHIRE is highly unique

2. ER Diagram

Figure 1 ER diagram

3. SQL statements

SQL is the structured query language, since it is the domain-specific language of the Relational Database Management System (RDBMS) querying results.

Using SQL for questing with a few syntax variations in Microsoft SQL Server, MySQL, Oracle, etc.

• SELECT

The SELECT statement is being used to records with or without a condition from the table.

· CREATE

To build a new table in an existing database, the CREATE statement is used. The creation of other database objects, including a stored procedure, function etc., is also using a CREATE statement

• INSERT

In the INSERT statement, a set of values is entered into a table in the database.

· UPDATE

The UPDATE statement is used for a table and is based on a requirement to change current values.

· DROP

For removing a table definition and all data, indexes, triggers, restrictions and specifications for permission for the table, SQL DROP TABLE is used.

· DELETE

Delete statement is used to erase the current table record depending on those criteria.

3.1 Create Table queries

create table Membership(

MEM_NUM int ,

MEM_FNAME varchar(80),

MEM_LNAME varchar(80),

MEM_STREET varchar(80),

MEM_TOWN varchar(80),

MEM_STATE varchar(80),

MEM_POSTALCODE int,

MEM_BALANCE int,

primary key (MEM_NUM)

);

create table HIRE(

HIRE_NUM int ,

HIRE_DATE date,

MEM_NUM int,

primary key(HIRE_NUM),

foreign key (MEM_NUM) references Membership(MEM_NUM)

);

create table CHARGE(

CHARGE_Code int ,

CHARGE_DESCRIPTION varchar(400),

CHARGE_RENTFEE int,

CHARGE_DAILYLATEFEE int,

primary key (CHARGE_Code)

);

create table TOY(

TOY_NUM int ,

TOY_Title varchar(15),

TOY_YEAR int,

TOY_COST int,

TOY_TYPE varchar(20),

CHARGE_CODE int,

primary key (TOY_NUM),

foreign key (CHARGE_CODE) references CHARGE(CHARGE_CODE)

);

create table TOYCOPY(

TOYCOPY_NUM int ,

TOYCOPY_INDATE date,

TOY_NUM int,

primary key (TOYCOPY_NUM),

foreign key (TOY_NUM) references TOY(TOY_NUM)

);

create table DETAILHIRE(

HIRE_NUM int ,

TOYCOPY_NUM int,

DETAIL_FEE int ,

DETAIL_DUEDATE date,

DETAIL_RETURNDATE date,

DETAIL_DAILYLATEFEE int,

primary key(HIRE_NUM,TOYCOPY_NUM),

foreign key (TOYCOPY_NUM) references TOYCOPY(TOYCOPY_NUM)

);

3.2 Insert data queries

INSERT INTO MEMBERSHIP VALUES (’11’,’john’,’B’,’calicut’,’Kolavaenna’,’krala’,’6703018′,’50200′);

INSERT INTO MEMBERSHIP VALUES (’12’,’Raju’,’PY’,’calicut’,’Bareekad’,’krala’,’6073010′,’90200′);

INSERT INTO MEMBERSHIP VALUES (’13’,’Mohan’,’B’,’peru’,’cheru’,’Dli’,’6073015′,’150200′);

INSERT INTO MEMBERSHIP VALUES (’14’,’Cajan’,’Balu’,’calicut’,’olavanna’,’krala’,’6073018′,’50200′);

INSERT INTO MEMBERSHIP VALUES (’15’,’jici’,’boby’,’calicut’,’Ueroke’,’krala’,’6073032′,’60200′);

INSERT INTO MEMBERSHIP VALUES (’16’,’shji’,’ST’,’calicut’,’Klperumrannna’,’krala’,’6073012′,’80200′);

INSERT INTO MEMBERSHIP VALUES (’17’,’Wachu’,’B’,’calicut’,’Molavanna’,’krala’,’6073018′,’50200′);

INSERT INTO HIRE VALUES (‘100′,’1999-09-21′,’11’);

INSERT INTO HIRE VALUES (‘101′,’2007-09-02′,’12’);

INSERT INTO HIRE VALUES (‘102′,’2002-09-01′,’13’);

INSERT INTO HIRE VALUES (‘103′,’1998-09-02′,’14’);

INSERT INTO HIRE VALUES (‘104′,’2003-02-01′,’15’);

INSERT INTO HIRE VALUES (‘105′,’2000-09-21′,’16’);

INSERT INTO HIRE VALUES (‘106′,’2005-09-21′,’17’);

INSERT INTO CHARGE VALUES (‘202′,’late’,’100′,’240′);

INSERT INTO CHARGE VALUES (‘201′,’late’,’500′,’540′);

INSERT INTO CHARGE VALUES (‘200′,’late’,’800′,’440′);

INSERT INTO CHARGE VALUES (‘203′,’late’,’700′,’560′);

INSERT INTO CHARGE VALUES (‘204′,’late’,’800′,’570′);

INSERT INTO CHARGE VALUES (‘205′,’late’,’890′,’26’);

INSERT INTO CHARGE VALUES (‘206′,’late’,’500′,’540′);

INSERT INTO TOY VALUES (‘100′,’kids’,’2000′,’100′,’silicon’, ‘201’);

INSERT INTO TOY VALUES (‘101′,’kids’,’2008′,’150′,’silicon’, ‘202’);

INSERT INTO TOY VALUES (‘102′,’infants’,’2004′,’130′,’plastic’,’203′);

INSERT INTO TOY VALUES (‘103′,’infants’,’2002′,’80’,’silicon’,’204′);

INSERT INTO TOY VALUES (‘104′,’infants’,’2000′,’90’,’silicon’,’205′);

INSERT INTO TOY VALUES (‘105′,’infants’,’2009′,’110′,’silicon’,’206′);

INSERT INTO TOY VALUES (‘106′,’infants’,’2007′,’108′,’silicon’,’200′);

INSERT INTO TOYCOPY VALUES (‘520′,’2003-09-23′,’100’);

INSERT INTO TOYCOPY VALUES (‘521′,’2000-08-12′,’101’);

INSERT INTO TOYCOPY VALUES (‘522′,’2002-11-23′,’102’);

INSERT INTO TOYCOPY VALUES (‘523′,’2003-10-19′,’103’);

INSERT INTO TOYCOPY VALUES (‘524′,’2000-05-03′,’104’);

INSERT INTO TOYCOPY VALUES (‘525′,’2002-01-11′,’105’);

INSERT INTO TOYCOPY VALUES (‘526′,’2003-01-16′,’106’);

INSERT INTO DETAILHIRE VALUES (‘333′,’520′,’2999′,’2000-12-08′,’2003-12-08′,’999’);

INSERT INTO DETAILHIRE VALUES (‘334′,’521′,’1999′,’2000-10-09′,’2003-10-09′,’959’);

INSERT INTO DETAILHIRE VALUES (‘335′,’522′,’2999′,’2000-08-10′,’2002-08-10′,’799’);

INSERT INTO DETAILHIRE VALUES (‘336′,’526′,’2999′,’2000-06-11′,’2001-06-11′,’969’);

INSERT INTO DETAILHIRE VALUES (‘337′,’525′,’2999′,’2000-04-12′,’2003-04-12′,’199’);

INSERT INTO DETAILHIRE VALUES (‘338′,’524′,’2999′,’2000-02-13′,’2002-02-13′,’399’);

INSERT INTO DETAILHIRE VALUES (‘339′,’523′,’2999′,’2000-01-14′,’2001-01-14′,’989’);

4. Result and Conclusion

select * from membership;

select * from hire;

select * from charge;

select * from toy;

select * from toycopy;

select * from detailhire;

References

• Barker, (2000). CASE*METHOD TM : Entity Relationship Modeling. Addison-Wesley Publishing Company, New York, New York,
• Ramez Elmasri and Shamkant B. Navathe. (2006), Fundamentals of DatabaseSystems (5th Edition). Addison Wesley,.
• J. Teorey and J. Fry & Yang, (2014), “A logical design methodology for relational databases using the extended entity-relationship model,” ACM Computing Survey, 18,2,

pp197-222.

create table Membership(
MEM_NUM int ,
MEM_FNAME varchar(80),
MEM_LNAME varchar(80),
MEM_STREET varchar(80),
MEM_TOWN varchar(80),
MEM_STATE varchar(80),
MEM_POSTALCODE int,
MEM_BALANCE int,
primary key (MEM_NUM)
);

INSERT INTO MEMBERSHIP VALUES (’11’,’john’,’B’,’calicut’,’Kolavaenna’,’krala’,’6703018′,’50200′);
INSERT INTO MEMBERSHIP VALUES (’12’,’Raju’,’PY’,’calicut’,’Bareekad’,’krala’,’6073010′,’90200′);
INSERT INTO MEMBERSHIP VALUES (’13’,’Mohan’,’B’,’peru’,’cheru’,’Dli’,’6073015′,’150200′);
INSERT INTO MEMBERSHIP VALUES (’14’,’Cajan’,’Balu’,’calicut’,’olavanna’,’krala’,’6073018′,’50200′);
INSERT INTO MEMBERSHIP VALUES (’15’,’jici’,’boby’,’calicut’,’Ueroke’,’krala’,’6073032′,’60200′);
INSERT INTO MEMBERSHIP VALUES (’16’,’shji’,’ST’,’calicut’,’Klperumrannna’,’krala’,’6073012′,’80200′);
INSERT INTO MEMBERSHIP VALUES (’17’,’Wachu’,’B’,’calicut’,’Molavanna’,’krala’,’6073018′,’50200′);

create table HIRE(
HIRE_NUM int ,
HIRE_DATE date,
MEM_NUM int,
primary key(HIRE_NUM),
foreign key (MEM_NUM) references Membership(MEM_NUM)
);

INSERT INTO HIRE VALUES (‘100′,’1999-09-21′,’11’);
INSERT INTO HIRE VALUES (‘101′,’2007-09-02′,’12’);
INSERT INTO HIRE VALUES (‘102′,’2002-09-01′,’13’);
INSERT INTO HIRE VALUES (‘103′,’1998-09-02′,’14’);
INSERT INTO HIRE VALUES (‘104′,’2003-02-01′,’15’);
INSERT INTO HIRE VALUES (‘105′,’2000-09-21′,’16’);
INSERT INTO HIRE VALUES (‘106′,’2005-09-21′,’17’);

create table CHARGE(
CHARGE_Code int ,
CHARGE_DESCRIPTION varchar(400),
CHARGE_RENTFEE int,
CHARGE_DAILYLATEFEE int,
primary key (CHARGE_Code)
);

INSERT INTO CHARGE VALUES (‘202′,’late’,’100′,’240′);
INSERT INTO CHARGE VALUES (‘201′,’late’,’500′,’540′);
INSERT INTO CHARGE VALUES (‘200′,’late’,’800′,’440′);
INSERT INTO CHARGE VALUES (‘203′,’late’,’700′,’560′);
INSERT INTO CHARGE VALUES (‘204′,’late’,’800′,’570′);
INSERT INTO CHARGE VALUES (‘205′,’late’,’890′,’26’);
INSERT INTO CHARGE VALUES (‘206′,’late’,’500′,’540′);

create table TOY(
TOY_NUM int ,
TOY_Title varchar(15),
TOY_YEAR int,
TOY_COST int,
TOY_TYPE varchar(20),
CHARGE_CODE int,
primary key (TOY_NUM),
foreign key (CHARGE_CODE) references CHARGE(CHARGE_CODE)
);

INSERT INTO TOY VALUES (‘100′,’kids’,’2000′,’100′,’silicon’, ‘201’);
INSERT INTO TOY VALUES (‘101′,’kids’,’2008′,’150′,’silicon’, ‘202’);
INSERT INTO TOY VALUES (‘102′,’infants’,’2004′,’130′,’plastic’,’203′);
INSERT INTO TOY VALUES (‘103′,’infants’,’2002′,’80’,’silicon’,’204′);
INSERT INTO TOY VALUES (‘104′,’infants’,’2000′,’90’,’silicon’,’205′);
INSERT INTO TOY VALUES (‘105′,’infants’,’2009′,’110′,’silicon’,’206′);
INSERT INTO TOY VALUES (‘106′,’infants’,’2007′,’108′,’silicon’,’200′);

create table TOYCOPY(
TOYCOPY_NUM int ,
TOYCOPY_INDATE date,
TOY_NUM int,
primary key (TOYCOPY_NUM),
foreign key (TOY_NUM) references TOY(TOY_NUM)
);

INSERT INTO TOYCOPY VALUES (‘520′,’2003-09-23′,’100’);
INSERT INTO TOYCOPY VALUES (‘521′,’2000-08-12′,’101’);
INSERT INTO TOYCOPY VALUES (‘522′,’2002-11-23′,’102’);
INSERT INTO TOYCOPY VALUES (‘523′,’2003-10-19′,’103’);
INSERT INTO TOYCOPY VALUES (‘524′,’2000-05-03′,’104’);
INSERT INTO TOYCOPY VALUES (‘525′,’2002-01-11′,’105’);
INSERT INTO TOYCOPY VALUES (‘526′,’2003-01-16′,’106’);

create table DETAILHIRE(
HIRE_NUM int ,
TOYCOPY_NUM int,
DETAIL_FEE int ,
DETAIL_DUEDATE date,
DETAIL_RETURNDATE date,
DETAIL_DAILYLATEFEE int,
primary key(HIRE_NUM,TOYCOPY_NUM),
foreign key (TOYCOPY_NUM) references TOYCOPY(TOYCOPY_NUM));

INSERT INTO DETAILHIRE VALUES (‘333′,’520′,’2999′,’2000-12-08′,’2003-12-08′,’999’);
INSERT INTO DETAILHIRE VALUES (‘334′,’521′,’1999′,’2000-10-09′,’2003-10-09′,’959’);
INSERT INTO DETAILHIRE VALUES (‘335′,’522′,’2999′,’2000-08-10′,’2002-08-10′,’799’);
INSERT INTO DETAILHIRE VALUES (‘336′,’526′,’2999′,’2000-06-11′,’2001-06-11′,’969’);
INSERT INTO DETAILHIRE VALUES (‘337′,’525′,’2999′,’2000-04-12′,’2003-04-12′,’199’);
INSERT INTO DETAILHIRE VALUES (‘338′,’524′,’2999′,’2000-02-13′,’2002-02-13′,’399’);
INSERT INTO DETAILHIRE VALUES (‘339′,’523′,’2999′,’2000-01-14′,’2001-01-14′,’989’);