• Join over 1.2 million students every month
  • Accelerate your learning by 29%
  • Unlimited access from just £6.99 per month

Star College Student Accommodation

Extracts from this document...

Introduction

Database System SD2052                              Assignment

  1. A fully annotated E-R diagram 1 and 2 showing the entities, primary  and foreign keys, composite keys and relationships.

Entity Relationship Diagram

An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database. ER diagrams often use symbols to represent three different types of information. Boxes are commonly used to represent entities. Diamonds are normally used to represent relationships and ovals are used to represent attributes.

ER 1

ER 2


b)  A Relational schema of your database in 3NF, clearly indicating   attributes, the data type of each attribute, primary and foreign keys, candidate keys, and which attributes are nullable, giving reasons.  List any assumptions you need to make.

        Normalization (BCNF)

In the relational model of databases, a candidate key of a relation is a minimal superkey and also is a field that can be part of the primary key to uniquely identify a record for each entity can have more candidate keys but it’s not necessary to use them all as part of the primary key in figure 3.1.

        Relation

Key attribute (Ordinary or circle shape) is a key attribute that is the unique and distinguishing characteristic of the entity in diagram below.


DatabaseSchema

        3NF

Student (SID, SNAME, SADDRESS, SAGE, SEX, NATIONALITY, ROOM_TYPE)

Apartment (AID, ROOM_TYPE, PLACE_NO, ROOM_NO, COST)

Hall Of Residences (HID, ROOM_TYPE, PLACE_NUMBER, ROOM_NO, COST)

Lease (LID, SID, DURATION, PLACE_NO, ROOM_NO)

Table

Primary Key

Foreign Key

Composite Key

Student

Sid

apartment

Aid

hall

Hid

lease

lid

sid

Apartment

aid

room_type

place_no

room_no

monthly_rent_rate

Student

Sid

Sname

Saddress

Sage

Sex

Nationality

Room

Hall

hid

room_type

place_no

room_no

monthly_rent_rate

Lease

lid

sid

duration

place_no

room_no

Assumptions

used for this scenario are as following

  • Student need to choose apartment or hall of residence
  • Student need information what room they stay and how long they stay with cost
...read more.

Middle

ROOM_TYPE     cost   ROOM_TYPE     cost

------------- ------ ------------- ------

A-Group by3   RM 150

A-Group by4   RM 130

A-Group by5   RM 100

                     H-Single room RM 80

  1. List the student name, room number and lease details.

SQL> select a.sname,b.* from student a,lease b where a.sid =b.sid;

SNAME      LID   SID DURATION  PLACE_NO ROOM_NO

---------- --- ----- ---------- -------- -------

JH low     L1   1001 1 Year     A-01     101-5

Khar Fai   L2   1002 1 Year     A-01     101-5

Kenny Lee  L3   1003 1 Year     A-01     101-5

Eugune Yap L4   1004 1 Year     A-01     101-5

Sam Low    L5   1005 1 Year     A-01     101-5

Suntheran  L6   1006 1 Semester A-01     101-4

Lloyd      L7   1007 1 Semester A-01     101-4

Mohamad    L8   1008 1 Semester A-01     101-4

Alice Tew  L9   1009 1 Semester A-01     101-4

Lina Koh   L10  1010 1 Year     A-01     101-3

Mindy Aw   L11  1011 1 Year     A-01     101-3

SNAME      LID   SID DURATION  PLACE_NO ROOM_NO

---------- --- ----- ---------- -------- -------

Karena Ng  L12  1012 1 Year     A-01     101-3

Chris Lee  L13  1013 1 Year     H-01     101

Alex Wong  L14  1014 1 Semester H-01     102

Chifford   L15  1015 1 Year     H-01     103

Jenny Khoo L16  1016 1 Semester H-01     104

Ken Ng     L17  1017 1 Year     H-01     105

Kogilar    L18  1018 1 Year     H-01     106

Jacky Tan  L19  1019 1 Semester H-01     107

Sabrina    L20  1020 1 Semester H-01     108

Ben Lee    L21  1021 1 Year     H-01     109

Silva      L22  1022 1 Year     H-01     110

  1. List the Room number, the apartment it is in, and the students who are living in those rooms.

select a.room_no,b.sname from lease a,student b where a.sid=b.sid and place_no like'A%';

ROOM_NO SNAME

------- ----------

101-5   JH low

101-5   Khar Fai

101-5   Kenny Lee

101-5   Eugune Yap

101-5   Sam Low

101-4   Suntheran

101-4   Lloyd

101-4   Mohamad

101-4   Alice Tew

101-3   Lina Koh

101-3   Mindy Aw

ROOM_NO SNAME

------- ----------

101-3   Karena Ng


e)

...read more.

Conclusion

insert into lease values('L4',1004,'1 Year','A-01','101-5');

insert into lease values('L5',1005,'1 Year','A-01','101-5');

insert into lease values('L6',1006,'1 Semester','A-01','101-4');

insert into lease values('L7',1007,'1 Semester','A-01','101-4');

insert into lease values('L8',1008,'1 Semester','A-01','101-4');

insert into lease values('L9',1009,'1 Semester','A-01','101-4');

insert into lease values('L10',1010,'1 Year','A-01','101-3');

insert into lease values('L11',1011,'1 Year','A-01','101-3');

insert into lease values('L12',1012,'1 Year','A-01','101-3');

insert into lease values('L13',1013,'1 Year','H-01','101');

insert into lease values('L14',1014,'1 Semester','H-01','102');

insert into lease values('L15',1015,'1 Year','H-01','103');

insert into lease values('L16',1016,'1 Semester','H-01','104');

insert into lease values('L17',1017,'1 Year','H-01','105');

insert into lease values('L18',1018,'1 Year','H-01','106');

insert into lease values('L19',1019,'1 Semester','H-01','107');

insert into lease values('L20',1020,'1 Semester','H-01','108');

insert into lease values('L21',1021,'1 Year','H-01','109');

insert into lease values('L22',1022,'1 Year','H-01','110');

SQL> column sage format 9999

SQL> column sid format 9999

SQL> column nationality format a11

SQL> /

Reference

http://cs-netlab-01.lynchburg.edu/courses/Oracle/SQLPlus.htm

http://cisnet.baruch.cuny.edu/holowczak/oracle/sqlplus/

http://www.orafaq.com/forum/t/128650/2/

http://homepages.uel.ac.uk/u0228569/UEL_files/SD%202052/SD2052.pdf

...read more.

This student written piece of work is one of many that can be found in our University Degree Computer Science section.

Found what you're looking for?

  • Start learning 29% faster today
  • 150,000+ documents available
  • Just £6.99 a month

Not the one? Search for your essay title...
  • Join over 1.2 million students every month
  • Accelerate your learning by 29%
  • Unlimited access from just £6.99 per month

See related essaysSee related essays

Related University Degree Computer Science essays

  1. Develop a Puzzle Website for users of three different age groups, Kids, Teenagers and ...

    Most exchanges are text-only. However, some services allow attachments. In order for IMing to work, both users (who must subscribe to the service) must be online at the same time, and the intended recipient must be willing to accept instant messages. (It is possible to set your software to reject messages.)

  2. Network report for Middlesex University. The current network design is a star topology with ...

    The Layer 3 switch on the left acts as the gateway router for half of the hosts in the subnet and the Layer 3 switch on the right acts as the gateway router for the other half. With MHSRP, packets from a particular host will always leave the building block via the active HSRP gateway.

  1. Evaluate the role of the Electoral College and explain why it has not been ...

    The Maine System is one idea put forward, as it is used in this state instead of the winner takes all system. This proposal gives a vote to a candidate for each congressional district they win in a state, and then an extra two votes to the candidate that wins the state overall.

  2. So in order to understand what the main areas where organisation should be aware ...

    by sending an ACK. o A connection is terminated after the full connection establishment process has been completed. This kind of scan is easily detected. Inspecting the target system log will show a number of connections and error messages immediately after each one of them was initiated.

  1. Was Indian indentureship a 'new system of slavery'?

    (Russell 1840)1 They discovered a contractual system of indentured labour from India, which proved satisfactory as it had a huge population and many Indians wanted to emigrate in hope of a better future. Some argue that the importation of labour was not only seen as a means of remedying the

  2. IT at the Table.

    enable the companies to access each other's intranets * have to make sure that there are strong help-desk capabilities because during a merger there is a much higher call volume. Aside from these technological portions of the formula, there are decisions to make on a case-by-case basis.

  1. The purpose of this project is to discuss the issues associated with the deployment ...

    In subsequent WAP specification releases, the WAP Forum addressed interoperability, established a certification program, and added various features in response to changes in market requirements and improvements in networks, devices and new technologies. WAP 2.0 is a next-generation set of specifications that, like previous releases, marks the WAP Forum's ongoing efforts to adopt the most recent Internet standards and protocols.

  2. Advanced Databases

    They all appeared to work except for the following :- Fields of type 'date' returned a value in the format 'yyyy-mm-dd 00:00:00'. However in order to Update a date field the format needs to be 'dd-mmm-yy' e.g. 28-feb-02. I decided that if I changed the format displayed to the 'dd-mmm-yy'

  • Over 160,000 pieces
    of student written work
  • Annotated by
    experienced teachers
  • Ideas and feedback to
    improve your own work