Description
2.1 Description
53 There are two assignments: (1) To exercise logging into Oracle and running a simple, prepared query: (2) To
54 create a relational implementation of an ER diagram.
55 2.2 Assignments
56 1. Start with this part. It is simple but if you run into difficulties they need to be resolved quickly.
57 Make sure that you know how to run SQL queries/commands on the Oracle systems at CIMS.
58 To do that, follow the instructions in How_To_Use_Oracle_At_CIMS.pdf.
59 You do not need to understand the queries/commands, just to make sure that you can do what’s requested.
60 if you run into difficulties doing that part of the assignment, please send email to William Wu, mailto:
61 yw4780@nyu.edu, explaining what the difficulties were. You will be helped.
62 2. You will need to create a relational implementation modifying relational04.architect, of the ER diagram
63 Assignment04.drawio satisfying the annotations in Assignment04.txt, using SQL Power Architect with
64 Crow’s feet notation and including the needed (but only the needed) annotations. Replace zk1 with your Net
65 ID and note what’s specified in Item 2a. You should follow exactly the ER diagram given and the example
66 of the University database we covered in class. Do not create better relational implementations if such are
67 possible by deviating from the ER diagram.
68 (a) Specify the following using SQL Power Architect and everything else that needs to be specified (if
69 anything) put in annotations by modifying Solution04.txt
70 • tables
71 • attributes, including for each that
72 – it must not be NULL (if applicable)
73 – it is a part of the primary key (if applicable)
74 – it is a part of a foreign key (if applicable)
75 It is not important for us what the domains of the attributes are, so use VARCHAR as the domain
76 for all attributes
77 • many-to-one mappings between tables (or one-to-one as applicable)
78 Please,
79 • Do not make any assumptions about the application beyond the specifications listed.
80 • Do not optimize your design, just follow the specification given.
81 • Do not use any other capabilities that SQL Power Architect provides; for example do not use
82 Index Properties or Remarks for Column Properties.
Typeset: 2021-10-02T17:04:00Z MD5: D5B78E17243B9F4BCD94B7CA4F8433C9
Page 3 of 3
83 • Anything that cannot be specified in your SQL Power Architect diagram, put as annotations in
84 Solution04.txt file, as described below.
85 • Do not put anything in the annotations that can be reflected in the SQL Power Architect imple86 mentation; make sure that the implementation reflects that.
87 (b) Modify the file Solution04.txt as specified in Item 1 of Section 3.
88 3 What to submit
89 Please upload 3 files, named exactly as specified and in the format exactly as specified.
90 1. Solution04.txt in the text format, by modifying the file in this assignment.
91 In that file
92 • Replace the Metadata with your information.
93 • State the required (and only the required) Annotations.
94 • State your responses by placing appropriate text after the item label as listed below (so your first item
95 will labeled “(a)”):
96 (a) State whether you have done what is requested in Item 1 of Section 2.2. If your answer is “Yes”,
97 just state that.
98 If not, explain why.
99 2. A file with a diagram in the native format as produced by SQL Power Architect. The file should be called
100 Relational04.architect.
101 3. A file with a PDF version of the diagram you have produced. The file should be called Relational04.pdf.
102 A An SQL Power Architect bug: Please read
103 SQL Power Architect sometimes produces inconsistent diagrams such as our R-S diagram in Figure 1. In R-S,
104 attribute A in S can be NULL but the left side of the line between R and S has a stroke | indicating that the
105 binary many-to-one mapping from S to R is total. Only R1-S1 and R2-S2 are correct. So make sure that your
106 diagram is correct, fixing what SQL Power Architect produces, if necessary. This is easy to do by modifying
107 Relationship Properties.
Figure 1: Read the text of Appendix A
Typeset: 2021-10-02T17:04:00Z MD5: D5B78E17243B9F4BCD94B7CA4F8433C9
