Databases and Networks

A DBMS is middleware that manages data centrally and handles control of simultaneous use by multiple users and programs, maintenance of consistency, and management of authentication and access rights. Document creation is application software, route selection is a router's job, and image compression belongs to image processing software; none describes a DBMS.

NoSQL is a general term for databases not based on the relational model (RDB); there are diverse types such as key-value and document stores, and many suit distributed processing of large data volumes. A table-based database built on the relational model describes an RDB, and an HDB is the separate hierarchical category. The SQL-only claim is also wrong.

The conceptual schema is the layer that defines the logical structure of the entire database. The external schema defines the views seen by users and programs, and the internal schema defines the physical storage layout on the storage devices. There is no layer called the physical schema in three-schema terminology. Dividing into three layers increases data independence.

An E-R diagram expresses entities and their attributes, the relationships between entities, and correspondence such as one-to-many (cardinality), and is used in conceptual database design. Processing steps are shown by flowcharts, device layouts by network diagrams, and logic operation inputs and outputs by truth tables.

The primary key is a column (or combination of columns) that uniquely identifies each row in a table, and neither duplicate values nor NULL is allowed. The statements permitting duplicates or NULL are therefore wrong. Referencing another table's primary key is the role of a foreign key, which is distinct. The patient ID in a patient table is a typical primary key.

A foreign key is a column that references another table's primary key; through the referential integrity constraint — it cannot hold values absent from the referenced table — it keeps tables consistent. Uniquely identifying rows is the primary key, speeding up searches is an index, and prohibiting duplicates is a unique constraint; none describes a foreign key.

Normalization splits tables so as to eliminate data duplication, with the aim of preventing contradictions (inconsistencies) during updates. Because split tables require joins, searches can actually become slower, so the claim that its purpose is faster searches is wrong. Encryption and shorter backup times are separate techniques unrelated to normalization.

First normal form is the form in which repeating groups — multiple values in a single field — have been eliminated. Separating dependence on part of the primary key (partial functional dependence) yields second normal form, separating dependence through non-key columns (transitive functional dependence) yields third normal form, and adding indexes has nothing to do with normalization.

The WHERE condition "age >= 65" is satisfied by 3 rows — Sato (72), Takahashi (65), and Tanaka (80) — and COUNT(*) returns the number of matching rows, so the result is 3. Note that Takahashi, at exactly 65, is included by ">=". 2 is the value from leaving out Takahashi, and 4 comes from wrongly including Suzuki (45).

The GROUP BY clause groups rows by department value, so the result has as many rows as there are distinct departments. The table contains 3 departments — internal medicine, surgery, and ophthalmology — so the result is 3 rows (internal medicine 3, surgery 2, ophthalmology 1). 6 is the original row count without GROUP BY; the question tests how grouping works.

INNER JOIN returns only the row pairs whose join condition (patient ID) matches. All 3 rows of the prescriptions table have matching rows in the patients table (1 for P001 and 2 for P003), so the result is 3 rows. Suzuki (P002), who has no prescriptions, does not appear. 2 comes from counting Takahashi's 2 rows as 1, and 5 confuses the answer with the simple total of row counts.

ORDER BY age DESC sorts by age in descending order (largest first), so the oldest, Takahashi (80), comes first. Sato (72) is second. Confusing it with ascending order (ASC) would pick the youngest, Suzuki (45). Remember firmly that DESC means descending and ASC means ascending.

Selection is the relational operation that extracts rows satisfying a condition; in SQL it corresponds to the WHERE clause. Projection extracts particular columns (the column list in SELECT), and join links multiple tables on a common column (JOIN). Union is a set operation combining the rows of 2 tables and differs from filtering rows.

Atomicity (indivisibility) is the property that a transaction's processing is either fully executed or not executed at all, allowing no half-finished state. Consistency means integrity is maintained, durability means completed results are never lost, and isolation means freedom from interference by other transactions.

Deadlock is the state in which 2 transactions each wait for the release of the resources the other has locked and both stall; it is resolved by forcibly rolling one of them back. Livelock is a state in which processing keeps yielding and never progresses, roll-forward is a failure recovery process, and a checkpoint is a recovery starting point; all are different.

Rollback cancels the updates of an abnormally ended transaction using the before-images in the log, restoring the state before it began. Commit finalizes updates on normal completion, roll-forward redoes updates up to just before a media failure using backups and the log, and two-phase locking is a concurrency control scheme.

Roll-forward restores the state as of the backup and then applies the after-images recorded in the log file in order, recovering to the state just before the failure. Rollback is the reverse process of cancelling updates, commit finalizes updates, and deadlock is a mutual lock wait; none is the recovery process from a media failure.

ETL stands for Extract, Transform, and Load — the series of processes that brings operational system data into the analytical DWH. OLAP is multidimensional aggregation and analysis of accumulated data, data cleansing corrects errors and inconsistent notation, and a dashboard is a BI tool's visualization screen; the flow through to loading is ETL.

The network layer performs route selection (routing) to the destination using IP addresses; IP is its representative protocol and the router its representative device. Frame transmission is layer 2, the data link layer; converting data representations is layer 6, the presentation layer; and converting to signals is layer 1, the physical layer.

TCP is a connection-oriented transport layer protocol that achieves reliable communication through delivery confirmation and retransmission control. UDP is connectionless and performs no confirmation or retransmission, which makes it lightweight and suited to real-time communication such as audio and video. The claims that TCP is physical layer and that UDP performs retransmission control are both wrong.

A MAC address is a 48-bit physical address assigned to a network interface, identifying devices on Ethernet and Wi-Fi. What DHCP assigns is configuration such as IP addresses, the 32-bit logical address is an IPv4 address, and what is obtained from a domain name is also an IP address (DNS name resolution); none of these describes a MAC address.

/26 corresponds to subnet mask 255.255.255.192, so the 4th octet is divided in steps of 64 (0, 64, 128, 192). Since 130 lies in the range from 128 up to but not including 192, the network address is 192.168.10.128. This can also be confirmed in binary: 130 is 10000010, and keeping the top 2 bits while zeroing the 6 host bits gives 10000000 = 128.

With /28 the host part is 32 − 28 = 4 bits, so there are 2 to the 4th = 16 addresses. Of these, the network address (host bits all 0) and the broadcast address (host bits all 1) cannot be assigned, leaving 16 − 2 = 14 for hosts. 16 forgets to subtract the 2, and 30 is the value for /27.

With mask 255.255.255.224 (/27) the 4th octet divides in steps of 32, and 66 lies in the range 64-95 (network address 192.168.1.64). 70 is in the same 64-95 range and so is on the same subnet. 60 is in 32-63, 100 in 96-127, and 130 in 128-159, all belonging to different subnets.

Private addresses fall in the 3 ranges 10.0.0.0-10.255.255.255, 172.16.0.0-172.31.255.255, and 192.168.0.0-192.168.255.255, and 192.168.100.1 falls within them. 172.32.0.1 lies outside the range, which ends at 172.31. 8.8.8.8 and 203.0.113.10 are on the global side.

DNS is the mechanism that maps domain names to IP addresses (name resolution), with DNS servers answering the queries. Automatic IP address assignment is DHCP, time synchronization is NTP, and mail transfer is SMTP — each a separate protocol. These role pairings appear often, so distinguish them reliably.

DHCP is the protocol that automatically hands out settings such as the IP address, subnet mask, and default gateway when a terminal connects. Name resolution is DNS, exchanging route information is done by routing protocols such as RIP and OSPF, and reachability checks are the role of ping, which uses ICMP.

The well-known port for HTTP is 80. SMTP is 25 (110 is POP3), DNS is 53 (25 is SMTP), and NTP is 123 (53 is DNS), so the other options mismatch the numbers. The port numbers of major services also matter for security configuration, so memorize them accurately.

SMTP is the protocol used for sending email and transferring it between mail servers. POP is the protocol by which users retrieve their own mail from a mail server and is not used for sending. HTTP transfers Web pages and NTP synchronizes time; neither has anything to do with mail transfer.

NTP is the protocol that synchronizes device clocks on the network to a reference server. In medical information systems, synchronizing the clocks of servers and terminals is important for keeping the times in clinical records and logs trustworthy. Web page transfer is HTTP and mail retrieval is POP, and NTP is not a file encryption protocol.

A router forwards packets between different networks based on IP addresses and its routing table; a switch with the equivalent function is an L3 switch. A hub relays received signals to every port, and an L2 switch forwards within the same LAN by looking at MAC addresses. A UTP cable is a transmission medium, not a device.

WEP is known to be easy to crack, so WPA and the stronger WPA2 are recommended. The SSID is a name identifying the access point (network), not an encryption key. Bluetooth is a separate short-range wireless standard from 802.11. In wireless LANs, interference occurs between radios on the same channel, so channel planning is necessary.

A VLAN is a technology that divides a network logically through switch configuration without changing the physical wiring. In healthcare institutions it is used to separate core networks such as the electronic medical record from administrative and guest networks. Wireless encryption standards are WPA2 and the like, cable types are UTP and STP, and the public line description is also wrong.

A VPN is a technology that uses encryption and related techniques to build a virtual private network over public lines such as the Internet; it is used for site-to-site links, remote access, and protecting communications for remote maintenance of medical devices. It is not a physical leased line itself, and its distinguishing point is that it costs less to build than leased lines. It has nothing to do with access points or DNS redundancy.

NAT is a technology that translates between private and global addresses; NAPT additionally uses port numbers so that 1 global address can be shared by multiple terminals. It is used when in-house terminals with private addresses go out to the Internet. Time synchronization is NTP, and mail encryption is the role of S/MIME and the like.