essay代写 > 北爱尔兰代写essay > ISYS1055/1057 Database Concepts 代写

ISYS1055/1057 Database Concepts 代写

  • 100%原创,高质量代写&Turnitin反剽窃检测-24小时在线客服微信&QQ:273427
  • ISYS1055/1057 Database Concepts S1/2013
    Assignment 1
    Questions in this assignment are regarding ER modelling, the Relational Model and SQL
    programming. The course material on topics RM1, ER1, SQL1 and SQL2 should be
    sufficient to answer questions in this assignment.
    Due time: 10:00pm 19 April 2013 Friday in Week 6.
    Worth: 50 marks in total, which is 20% of the overall assessment for the course.
    Assignments 1 and 2 together is a hurdle to pass the course.
    Question 1. (29 marks).
    You are asked to design the train service management database for M3 Transport
    Corporation. Description of the miniworld is given below.
    • There are several train lines from the city centre to suburbs. Each line has a unique
    name and is also described by the distance between the city centre and the terminus
    station. For example the Lake Villa line runs from the city centre to Lake Villa and
    has a distance of 30 kilometres.
    • Each station has a unique name, a contact phone number. A station is on at least one
    train line and some stations are on several train lines.
    • Standard station facilities include vending machines, newspaper machines, and ticket
    machines. Each machine has a unique facility number. Some premium stations have
    such facilities.
    • Trains are identified by unique train Nos. Other information about trains includes
    passenger capacity, model, manufacturer and year-made. A train line has several
    trains and a train is assigned to one line if not under maintenance.
    • Employees of M3 are described by unique employee Nos, and the first name, last
    name, and birth date. Drivers are assigned to work on several train lines, and a line
    has several drivers.
    • During peak hours (7:30am—9:30am on work days) premium stations have
    supervisors to provide customer service. A station has a supervisor and an employee
    can supervise only one station.
    Some typical applications for the database include:
    • A customer wants to go somewhere near a station. S/he wants to find out the train line
    for his/her trip.
    • A customer lives close to a train line. But s/he wants to go to a station on a different
    line. S/he wants to find out if this is possible and if so, at which station to change
    line?
    • The M3 management wants a report of contact details of station supervisors and/or
    drivers for a specific train line.
    1.1 (15 marks in total). According to the given description, construct an Entity
    Relationship (ER) diagram for the database, and make assumptions where necessary.
    Your design shall at least support the given typical applications. You must represent
    entities, relationships and their attributes, and all applicable constraints in your ER
    diagram. Explain any concepts in the description that can not be expressed in the ER
    diagram.
    Your ER diagram must use notations in the lecture notes and should not be hand
    drawn. ER diagrams using other notations will receive zero mark for Question 1. You
    are strongly advised to use the ER diagramming tool Visio or Dia, which you can
    download from the course Blackboard (Course Content   ER Diagram Tools).
    1.2 (14 marks in total.) Convert your ER diagram in the previous question into a
    relational database schema. For each relation in your relational database schema, you
    should
    • underline the primary key and denote any foreign keys with asterisks (*), and
    • explain the corresponding ER construct to relation mapping rules. For example,
    this relation X is mapped from the 1:M relationship between Y and Z in the ER
    diagram and attribute x 1 is the primary key because … and x 1 is a foreign key
    referring to the primary key for entity M.
    Question 2. (21 marks)
    The Online Movies DataBase (OMDB) is as follows:
    MovieInfo (mvID, title, rating, year, length, studio)
    DirectorInfo(directorID, firstname, lastname)
    MemberInfo(username, email, password)
    ActorInfo(actorID, firstname, lastname, gender, birthplace)
    CastInfo(mvID*, actorID*)
    DirectInfo(mvID*, directorID*)
    GenreInfo(mvID*, genre)
    RankingInfo(username*, mvID*, score, voteDate)
    To distinguish tables from those in the Village Cinema database for lectures, all tables are
    named with the suffix Info. Members rank movies of scores 1—5. Primary keys are
    underlined and foreign keys are marked with *. The SQL script for defining and
    populating the database omdb2-all.sql can be downloaded from the course blackboard
    (Course content  Databases).
    Write ONE SQL query for each of Questions 2.1—2.6 below; otherwise you will receive
    zero mark for the question. Each component of an SQL statement must be on a separate
    line. For example,
    SELECT mvid
    FROM movieinfo
    WHERE year=2000;
    Do not include the result of the query or the script used to create the tables. Your query
    should not produce duplicates in output but use DISTINCT only if necessary. Each
    question below is worth 3 marks.
    2.1 How many studios are there in total?
    2.2 Display the first name and last name of actors born in the US. Output should be in
    alphabetical order of last name and then first name. Note that there is known data
    inconsistency in the birthplace for actors. For example, a birthplace can be written as
    "US" or "USA".
    2.3 Compute the number of movies produced in each studio. Output should include
    studio and the number of movies produced in the studio, in decreasing order of
    number of movies.
    2.4 List the first name and last name of directors who have directed movies produced in
    the Studio "Paramount Picture". Output should be in alphabetical order of last name
    and then first name. You must use the NATURAL JOIN operator.
    2.5 Find the member(s) that have cast the largest number of votes. Output the username
    of such member(s). You should not use the MAX function.
    2.6 List the ActorID of actors who have appeared in movies produced by the studio
    “Sony Picture”. You must use the EXISTS operator.
    2.7 Specify the syntax errors in the SQL query below. Give a correct query and explain
    what the query does. Literal explanation like “Select title and number of actors from
    CastInfo joined with MovieInfo grouped by mvid” will receive zero mark.
    select title, count(actorid)
    from CastInfo, MovieInfo
    group by mvid
    where CastInfo.mvid=MovieInfo.mvid;
    Submission and Marking
    University Plagiarism Statement
    This is an individual assignment. Plagiarism in oral, written or visual presentations is the
    presentation of the work, idea or creation of another person, without appropriate
    referencing, as though it’s one’s own. Plagiarism is not acceptable and may result in
    charges of academic misconduct which carries a range of penalties. It is also a
    disciplinary offence for students to allow their work to be plagiarised by another student.
    For details, please check the course guide.
    Initial submission will be due in your tute/lab class in Week 5. You are strongly advised
    to complete at least one third of total number of questions for the initial submission.
    Your initial submission must be a .zip file via the WebLearn assignment submission
    system and you must select “Xiuzhen Zhang” as the tutor. The .zip file should contain a
    .pdf file for Question 1 and a plain text .sql file for Question 2. All files should be named
    after your student number. For example, if your student number is s123, you should
    submit s123.zip, which contains s123_1.pdf and s123_2.sql.
    Final submission will be due at 10:00pm 19 April 2013 Friday in Week 6. Submit one
    .zip file via the WebLearn assignment submission system and you must select “Xiuzhen
    Zhang” as the tutor. The .zip file should contain a .pdf file for Question 1 and a plain text
    .sql file for Question 2. All files should be named after your student number. The final
    submission will automatically override your initial submission.
    Marking
    • Initial submission is a hurdle. You must show your submission receipt email to staff
    in your tute/lab class in Week 5. Failing to do so means that you will receive zero
    mark for the assignment. Feedback to your initial submission will be provided in your
    tute/lab class in Week 5.
    • Final submission not in the required format will incur deduction of 5 marks. A
    penalty of 10% per day of the toal marks will apply for each working day being late.
    After 5 working days, you will receive zero mark for the assignment. Mark for final
    submission will be given in two weeks.
    •  SQL queries will be marked for correctness and understandability.
    •  The ER diagram will be marked for correctness and completeness for representing
    concepts in the given description.