Comp sci exam flashcards

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/152

encourage image

There's no tags or description

Looks like no tags are added yet.

153 Terms

1
New cards

What is the purpose of normalising data?

To organise data into separate related tables so that redundancy (duplicated data) is reduced, data anomalies are avoided, and data integrity and consistency are improved.

2
New cards

What are the three data anomalies?

Insert anomaly (can't add data without other unrelated data), update anomaly (changing one fact means changing many rows, risking inconsistency), delete anomaly (deleting a row accidentally removes other useful data).

3
New cards

Give an example of an INSERT anomaly.

In a flat table with order and customer data together, you can't add a new customer until they place an order, or a new product until it is ordered.

4
New cards

Give an example of an UPDATE anomaly.

If a vendor's phone number is stored on every row they appear in, changing it means editing many rows. Missing one leaves inconsistent data.

5
New cards

Give an example of a DELETE anomaly.

If the only order containing a product is deleted, all the details of that product (name, price, vendor) are lost too.

6
New cards

What are the requirements for a relation to be in 1NF?

Every field holds a single atomic value (no multi-valued fields or repeating groups), no repeating columns, and each record is unique with a primary key.

7
New cards

What are the requirements for a relation to be in 2NF?

Must already be in 1NF AND have no partial dependencies: every non-key attribute depends on the WHOLE primary key (only matters when there is a composite key).

8
New cards

What are the requirements for a relation to be in 3NF?

Must already be in 2NF AND have no transitive dependencies: non-key attributes must depend only on the primary key, not on another non-key attribute.

9
New cards

What is a partial dependency?

A non-key attribute that depends on only PART of a composite primary key. E.g. in OrderLine(OrderID, ProductID, ProductName, Qty), ProductName depends only on ProductID.

10
New cards

What is a transitive dependency?

When a non-key attribute depends on another non-key attribute rather than directly on the primary key. E.g. ProductID -> VendorName -> VendorPhone (VendorPhone depends on VendorName).

11
New cards

What is a composite key?

A primary key made of two or more fields combined to uniquely identify a record, e.g. (OrderID, ProductID) in an OrderLine table.

12
New cards

What is a primary key?

A field (or fields) that uniquely identifies each record in a table. It must be unique and not null.

13
New cards

Describe the role of a foreign key in database design.

A field in one table that matches the primary key of another table. It creates the link between the two tables and enforces referential integrity.

14
New cards

What are the steps to normalise data to 3NF?

1) Identify the unnormalised data and its key. 2) 1NF: remove repeating groups and multi-valued fields. 3) 2NF: split out attributes that depend on only part of a composite key. 4) 3NF: split out attributes that depend on non-key attributes. 5) Link the tables with foreign keys.

15
New cards

How do you write tables in relation notation?

TableName(PrimaryKey, Attribute, Attribute, ForeignKey). Underline primary keys and mark foreign keys with * or FK, e.g. Order(OrderID, CustomerID*, OrderDate).

16
New cards

How can you spot data that is NOT in 1NF?

Look for cells with multiple values, blank cells meaning 'same as above' for repeating groups, or repeated columns like Product1, Product2, Product3.

17
New cards

Ellyse's spreadsheet has the columns OrderID, FirstName, LastName, ProductID, ProductName, UnitPrice, Quantity, VendorName, VendorPhone. Order 2001 (Alice White) is spread over two rows with the OrderID and name left blank on the second row. Which normal form does this break and why?

1NF. Order 2001 contains a repeating group (products P001 and P002 across multiple rows). Each row must be a complete record with the order and customer details filled in.

18
New cards

Ellyse's spreadsheet (OrderID, FirstName, LastName, ProductID, ProductName, UnitPrice, Quantity, VendorName, VendorPhone): which dependencies stop it being in 3NF?

ProductName and UnitPrice depend only on ProductID (a partial dependency on the composite key OrderID + ProductID). VendorName depends on ProductID, and VendorPhone depends on VendorName (a transitive dependency).

19
New cards

Convert Ellyse's data to third normal form (3NF) using relation notation. Columns: OrderID, FirstName, LastName, ProductID, ProductName, UnitPrice, Quantity, VendorName, VendorPhone. Example rows: order 2001 Alice White bought P001 Smartphone $600 x3 (TechCorp, (08) 7245 7890) and P002 Laptop $1200 x1 (CompWorld, (08) 9875 4210). The first relation is done for you: Customer(CustomerID, FirstName, LastName).

Customer(CustomerID, FirstName, LastName) ; Order(OrderID, CustomerID) ; OrderLine(OrderID, ProductID, Quantity) with composite key (OrderID, ProductID) ; Product(ProductID, ProductName, UnitPrice, VendorID) ; Vendor(VendorID, VendorName, VendorPhone)

20
New cards

In your 3NF answer for Ellyse's data, why is Quantity in OrderLine and not in Product?

Quantity depends on BOTH the order and the product (how many of that product were in that order), so it needs the composite key (OrderID, ProductID).

21
New cards

In your 3NF answer for Ellyse's data, why is VendorPhone in its own Vendor table?

VendorPhone depends on the vendor, not the product. Keeping it in Product would be a transitive dependency (ProductID -> VendorName -> VendorPhone). A separate Vendor table fixes this and avoids update anomalies.

22
New cards

A music database has a table with columns TrackID, TrackName, AlbumID, AlbumName, BandMembers. State the requirement(s) for a relation to be in 1NF, 2NF and 3NF.

1NF: atomic values only (no multi-valued fields or repeating groups) and a unique key. 2NF: must already be in 1NF and have no partial dependencies. 3NF: must already be in 2NF and have no transitive dependencies.

23
New cards

In the music table (TrackID, TrackName, AlbumID, AlbumName, BandMembers), what breaks 1NF?

BandMembers is a multi-valued attribute (several members stored in one field). It must be split out into its own table with atomic values.

24
New cards

In the music table (TrackID, TrackName, AlbumID, AlbumName, BandMembers), what breaks 3NF?

AlbumName depends on AlbumID, not directly on TrackID (a transitive dependency). Move it to Album(AlbumID, AlbumName) and keep AlbumID as a foreign key in Track.

25
New cards

Give a possible 3NF design for the music table (TrackID, TrackName, AlbumID, AlbumName, BandMembers).

Track(TrackID, TrackName, AlbumID) ; Album(AlbumID, AlbumName) ; Member(MemberID, MemberName) ; AlbumMember(AlbumID, MemberID*) - the last table resolves the many-to-many between albums and band members.

26
New cards

How do you resolve a many-to-many (M:N) relationship?

Create a linking (junction) table whose primary key is a composite of the two foreign keys. This splits the M:N into two one-to-many relationships.

27
New cards

Give an example of resolving a many-to-many relationship.

Students and Subjects (many students take many subjects) becomes Enrolment(StudentID, SubjectID) with a composite key, giving Student 1:M Enrolment and Subject 1:M Enrolment.

28
New cards

What is a quick tip for answering normalisation questions?

For each non-key field ask: 'What does this depend on?' If the whole key, it stays. If only part of the key, or another non-key field, move it to its own table. Then add foreign keys to link the tables.

29
New cards

What are the three types of relationship in a database?

One-to-one (1:1), one-to-many (1:M), many-to-many (M:N). A 3NF design normally only has 1:M because M:N is resolved with a linking table.

30
New cards

What does crow's foot notation show in an ERD?

Entities as boxes with their attributes, and relationship lines with symbols showing cardinality (the crow's foot means 'many', a single bar means 'one', a circle means 'optional/zero').

31
New cards

How do you mark keys in an ERD?

Primary key at the top of the entity, labelled PK (or underlined). Foreign keys labelled FK. List all non-key fields below.

32
New cards

What is cardinality?

The number of records in one entity that can relate to records in another: 1:1, 1:M or M:N. Read it in both directions (e.g. one customer places many orders, each order belongs to one customer).

33
New cards

In a one-to-many relationship, which table holds the foreign key?

The 'many' side. E.g. CustomerID is a foreign key in the Order table (one customer, many orders).

34
New cards

Create an ERD using crow's foot notation for data normalised to 3NF. Ensure you include all entities, cardinalities and all key and non-key fields. What do you need to include?

Every entity (table) from your 3NF design, ALL fields from the source data, PK and FK labels, relationship lines with correct crow's foot cardinality, and any linking tables for M:N relationships.

35
New cards

What is a data dictionary and what is its purpose?

Documentation for developers listing each field in a table: name, data type, size/format, description and constraints. It ensures everyone builds and uses the database consistently.

36
New cards

What columns does a data dictionary have?

Element name, data type, size/format, description, constraint.

37
New cards

What are examples of constraints in a data dictionary?

Required (not null), unique, must be after today's date, must be after StartDate, range check, foreign key reference.

38
New cards

Complete this data dictionary for an Exhibitions table: ExhibitionID (Int, 5, Required); ExhibitionName (blank type, blank size, 'Name of the exhibition', Required); StartDate (Date, YYYY-MM-DD, 'Start date of the exhibition', Required, must be after today's date); EndDate (Date, YYYY-MM-DD, 'End date of the exhibition', blank constraint); and a blank row (Int, 5, 'Identifier for the gallery room', Required). Fill in the five blanks.

ExhibitionName: Text/String, size e.g. 50. EndDate constraint: Required, must be after StartDate. Last row element name: RoomID (or GalleryRoomID), a foreign key to a Room table. The Int, 5 and Required are already given.

39
New cards

What is the difference between a flat file and a relational database?

A flat file stores all data in one table with lots of repeated data and anomalies. A relational database splits data into linked tables using keys, reducing redundancy and improving integrity.

40
New cards

Outline two benefits Ellyse would gain by switching from a spreadsheet to a relational database management system.

Reduced data redundancy (e.g. vendor details stored once), improved data integrity and consistency, easier updating, secure controlled access for multiple users, fast queries using SQL.

41
New cards

What is the role of an RDBMS?

Software that creates and manages relational databases and handles access to the data (queries, security, multiple users) so applications are independent of how the data is stored.

42
New cards

What is the difference between data and information?

Data is raw facts and figures with no context. Information is data that has been processed or organised so it is meaningful and useful.

43
New cards

How are entity, attribute, table, field and record related?

An entity (thing of interest) is implemented as a table. An attribute is a field (column). A record is one row of data. Hierarchy: field -> record -> table -> database.

44
New cards

What data types are used in databases?

Integer (whole numbers), float (decimals), Boolean (true/false), text (strings), date.

45
New cards

You are designing a program to manage a library's book inventory, storing the number of copies of each book and the purchase price (dollars and cents). Which data type would you use for each and why?

Number of copies: integer, because it is a whole number. Book price: float (decimal), because it includes cents (a fractional part).

46
New cards

List the factors that influence the integrity of data.

Currency (up to date), authenticity (genuine/trusted source), relevance (suits the purpose), accuracy (correct/free of errors), outliers (extreme values that may need cleaning).

47
New cards

What is the relationship between the validity and accuracy of data?

Valid data meets the rules (right type, range, format) but may still be inaccurate. Valid does NOT guarantee accurate. Accurate data must also be valid.

48
New cards

What is outlier cleaning?

Identifying values far outside the expected range and checking, correcting or removing them so they don't distort results.

49
New cards

What is the basic structure of an SQL SELECT statement?

SELECT columns FROM table WHERE condition ORDER BY column;

50
New cards

Describe the purpose of an aggregate function in SQL and identify two aggregate functions from the syllabus.

It performs a calculation on a set of values in a column and returns a single value. Examples: COUNT, SUM, AVG, MIN, MAX.

51
New cards

Pool database (Client, Pool, Installation tables): what value does this return? SELECT COUNT(client_id) FROM Client WHERE last_name LIKE "S%" (clients: Smith, Brown, Johnson, Stone)

2 (Smith and Stone). LIKE "S%" matches last names starting with S.

52
New cards

Pool database: what value does this return? SELECT MIN(cost) FROM Pool WHERE max_depth > 1.4 (pools: Splash max_depth 1.75 cost 17000; Blue Dreams 1.75 cost 23500; Norfolk 1.4 cost 16000; Abrolhos 1.8 cost 31000)

  1. Norfolk (max_depth 1.4) is excluded because 1.4 is not greater than 1.4, so Splash at 17000 is the minimum.
53
New cards

Pool database: what does this return? SELECT name FROM Pool INNER JOIN Installation ON Pool.pool_id = Installation.pool_id WHERE client_id = 1001 AND paid = TRUE (client 1001 has install 20020 for pool 301 Splash paid TRUE, and install 21050 for pool 368 Abrolhos paid FALSE)

Splash. Only install 20020 meets both conditions (client 1001 and paid = TRUE).

54
New cards

Jake wants the names of clients and the type of pool they ordered that are due to be installed in December. His query is SELECT Client.first_name, Client.last_name, Pool.name FROM Pool, ClientPool, Client WHERE install_date >= "1/12/2024" AND install_date <= "31/12/2024". Explain the error.

The table ClientPool doesn't exist (it should be Installation), and there are NO join conditions linking the tables, so the result is a Cartesian product. The dates should also be in the database's date format (YYYY-MM-DD) or use BETWEEN.

55
New cards

Rewrite Jake's SQL (Client, Pool, Installation tables) so it returns the clients and pool types being installed in December 2024.

SELECT Client.first_name, Client.last_name, Pool.name FROM Client INNER JOIN Installation ON Client.client_id = Installation.client_id INNER JOIN Pool ON Installation.pool_id = Pool.pool_id WHERE install_date >= '2024-12-01' AND install_date <= '2024-12-31';

56
New cards

How does an INNER JOIN work?

It combines rows from two tables where the join condition matches (usually primary key = foreign key), returning only rows with a match in both tables.

57
New cards

Write an SQL statement to find the total number of warranty claims that are currently open.

SELECT COUNT(*) FROM Claim WHERE Status = 'Open'; (use the real table and field names from the question's data)

58
New cards

RoboVac Australia must recall all RoboVac Mini products that have been purchased. Write an SQL statement to find the name and phone number of all affected customers.

SELECT DISTINCT Customer.Name, Customer.Phone FROM Customer INNER JOIN Purchase ON Customer.CustomerID = Purchase.CustomerID INNER JOIN Product ON Purchase.ProductID = Product.ProductID WHERE Product.ProductName = 'RoboVac Mini'; (join every table along the path from customer to product)

59
New cards

What does LIKE do in SQL and what are the wildcards?

LIKE searches for a pattern. % matches any number of characters ("S%" starts with S), _ matches exactly one character.

60
New cards

Which operators can be used in a SQL WHERE clause?

=, !=, >,

61
New cards

What does ORDER BY do?

Sorts the results by a column, ascending (ASC, the default) or descending (DESC).

62
New cards

What are the three program control structures?

Sequence (steps in order), selection (IF/ELSE decisions), iteration (loops: FOR/WHILE).

63
New cards

What is the difference between a local and a global variable?

Global variables are declared outside functions and visible everywhere (to change one inside a function use the global keyword). Local variables exist only inside the function they are created in.

64
New cards

What is the difference between a parameter and an argument?

A parameter is the variable named in the function definition. An argument is the actual value passed in when the function is called.

65
New cards

Why use functions (modular coding)?

They break a program into smaller reusable parts that are easier to develop, test, understand, modify and debug. Aim for one logical task per module.

66
New cards

What are the four data types in the syllabus?

Integer (whole numbers), float (decimals), string (text), Boolean (True/False).

67
New cards

What are the arithmetic operators?

  • - * / and % (MOD, the remainder). E.g. 7 % 2 = 1.
68
New cards

What are the relational operators?

= (== in Python), !=, >,

69
New cards

What is the order of precedence for logical operators?

NOT first, then AND, then OR. A AND B is True only if both are True, A OR B is True if at least one is True.

70
New cards

What is a one-dimensional array?

A data structure holding multiple values under one name, accessed by index starting at 0. E.g. scores = [5, 8, 2] and scores[0] is 5.

71
New cards

Describe the standard algorithm for finding the minimum value in an array.

Set min to the first element, loop through the array, and if an element < min then min = element. Return min. (To find the POSITION of the minimum, store the index instead.)

72
New cards

Describe the standard algorithm for adding the contents of an array.

Set total = 0, loop through each element adding it to total, then output total.

73
New cards

Describe the standard algorithm for loading an array and printing its contents.

Loop to INPUT each value into the array, then loop again through every element printing it.

74
New cards

What are the steps for processing a sequential text file?

Open the file (read r, write w, or append a), read and process data or write content, then close the file. Write overwrites the file, append adds to the end.

75
New cards

How are characters represented as numbers?

Characters are stored as numbers, e.g. 'A' = 65 (decimal) = 41 (hex) = 01000001 (binary). In Python use ord('A') and chr(65).

76
New cards

What are binary, decimal and hexadecimal?

Binary is base 2 (0-1), decimal is base 10, hexadecimal is base 16 (0-9 then A-F, where A=10 … F=15).

77
New cards

List the good programming practices from the syllabus.

Validate input before processing, meaningful variable names, constants for readability/maintenance, comments to explain code, appropriate standard control structures, indentation and white space, one logical task per module, meaningful module names, exception handling.

78
New cards

Vikki reviews this code: def s(a): ⏎ m = a[0] ⏎ for v in a: ⏎ if v < m: ⏎ m = v ⏎ return m. Other than comments, outline two issues that could be improved by good programming practice.

The function name 's' and the variables a, m, v are not meaningful names. There is no input validation or exception handling (an empty array causes an error at a[0]).

79
New cards

Rewrite Vikki's minimum function (def s(a): m = a[0] …) resolving the issues and including two comments.

def find_minimum(numbers): ⏎ # Return the smallest value in a list ⏎ if len(numbers) == 0: ⏎ return None ⏎ minimum = numbers[0] # start with the first value ⏎ for value in numbers: ⏎ if value < minimum: ⏎ minimum = value ⏎ return minimum

80
New cards

Why should you use constants in a program?

Constants (e.g. HOUSE_AREA = 250) avoid 'magic numbers', make code readable, and mean a value only needs changing in one place.

81
New cards

What is exception handling?

Code (try/except) that catches runtime errors, such as invalid input, so the program doesn't crash and can give a useful message.

82
New cards

What are the four phases of the framework for development?

Investigate (problem description, requirements, Gantt chart), design (data structures, algorithms, test plans), develop (code, debug, unit testing), evaluate (user acceptance testing, developer retrospective).

83
New cards

What is a Gantt chart used for?

Showing a development schedule: tasks, their durations, order and dependencies on a timeline.

84
New cards

What is a structure chart?

A design tool showing how a program is broken into modules and how they relate in a hierarchy.

85
New cards

What is a stub?

A placeholder for an incomplete module so that other modules can be tested before it is finished.

86
New cards

What are the benefits of structured algorithms?

Ease of development, ease of understanding, ease of modification.

87
New cards

Write an algorithm in pseudocode that asks a user to enter a student's grade and prints "Pass" if the grade is 50 or above and "Fail" if it is below 50.

BEGIN ⏎ OUTPUT "Enter grade" ⏎ INPUT grade ⏎ IF grade >= 50 THEN ⏎ OUTPUT "Pass" ⏎ ELSE ⏎ OUTPUT "Fail" ⏎ END IF ⏎ END

88
New cards

Trace this pseudocode with num inputs 9, 4, 5: min = 100, total = 0, average = 0; FOR i = 1 TO 3: INPUT(num), total = total + num, IF min > num THEN min = num; END FOR; average = total / 3. What are the values at each iteration?

i=1: num=9, total=9, min>num True, min=9. i=2: num=4, total=13, True, min=4. i=3: num=5, total=18, 4>5 False, min stays 4. After the loop, average = 18/3 = 6.

89
New cards

What is a trace table (desk checking)?

Manually stepping through an algorithm with test data, recording the value of each variable after every line, to find logic errors without running the code.

90
New cards

What test data should be chosen to test an algorithm?

Data testing all pathways through the algorithm, boundary conditions ('at', 'above' and 'below' decision values), data where the answer is known, and type and range checks.

91
New cards

What boundary test data would you use for 'Pass if grade >= 50'?

49 (below), 50 (at), 51 (above).

92
New cards

What are the three types of coding errors?

Syntax error (breaks language rules, code won't run), runtime error (crashes while running, e.g. divide by zero), logic error (runs but gives the wrong result).

93
New cards

How do debugging print statements help?

Extra print statements show which sections of code have executed and the contents of variables at a particular point.

94
New cards

What is the output? word = 'python' ⏎ word = word * 2 ⏎ print(word)

pythonpython (multiplying a string repeats it).

95
New cards

What is the output? numbers = [1, 2, 3, 4, 5, 6] ⏎ total = 0 ⏎ for num in numbers: ⏎ if num % 2 == 1: ⏎ total += num ⏎ print(total)

9 (the odd numbers 1 + 3 + 5).

96
New cards

What is the output? numbers = [1, 7, 3, 7, 4, 2, 6, 5, 0, 8] ⏎ total = 0 ⏎ for i in range(3): ⏎ total += numbers[numbers[i]] ⏎ print(total)

  1. i=0: numbers[1]=7. i=1: numbers[7]=5. i=2: numbers[3]=7. Total 7+5+7 = 19.
97
New cards

What is the output? a = True ⏎ b = True ⏎ c = False ⏎ print((a and b) or c)

True.

98
New cards

RoboVac: write a Python function recharge_battery that resets the global variable battery_level back to 100.

def recharge_battery(): ⏎ global battery_level ⏎ battery_level = 100 (the 'global' keyword is essential to change a global variable)

99
New cards

RoboVac: initialise weekly_schedule with this schedule (Sunday no cleaning, Monday full clean, Tuesday partial 20%, Wednesday no cleaning, Thursday full clean, Friday partial 20%, Saturday full clean). Percentages are stored as integers.

weekly_schedule = [0, 100, 20, 0, 100, 20, 100]

100
New cards

RoboVac: write can_complete_cleaning(cleaning_percentage), which calculates the battery needed (2% per square metre, HOUSE_AREA = 250 global constant), checks the current battery_level, and if sufficient subtracts the usage and returns True, otherwise returns False.

def can_complete_cleaning(cleaning_percentage): ⏎ global battery_level ⏎ area = HOUSE_AREA * cleaning_percentage / 100 ⏎ required = area * 2 ⏎ if battery_level >= required: ⏎ battery_level = battery_level - required ⏎ return True ⏎ else: ⏎ return False